CODEWITHSUNDEEP
excelpowerbipowerquery
battery514
battery514

Reputation: 249

Find Next Date/Day in Power Query

I have two tables for which I need to find a date. In other words, I am trying to solve two problems which I feel are closely related.

Table 1: Has today's date in a column. From this date I need to populate another column with the date of the next Friday. In other words, if today is 10/6/19, I want the new column to populate with 10/11/19.

Table 2: Has the day number of "1" in a column. From this number, I need to populate another column with next month's date using that day number. In other words, if today is 10/6/19, I want the new column to populate with 11/1/19.

Any help would be much appreciated. I know how to do this in Excel, but after reviewing a bunch of posts and a list of M date functions, I'm at a loss.

Thanks!

Upvotes: 0

Views: 1617

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

Your question is not entirely clear. Perhaps the following will help.

If you have a column of dates, to add a column which will show the "next Friday", the Custom Column formula:

=Date.AddDays([dates],7-Date.DayOfWeek([dates],5))

Your second question, if you have a column of numbers (eg Index), a formula which will return the date n months in the future on the same date as today:

=Date.AddMonths(DateTime.LocalNow(),[Index])

Consider using .FixedLocalNow after reading about the difference

enter image description here

Upvotes: 1

Related Questions