Search code examples
datetimepowerquerym

Power Query (M) - 12 Months Previous to the start of current month

I have a set of custom columns in a Power Query table, which need the rows to be populated with the following date:

The start of the current month - 12 months ago (11 months ago, 10 months ago etc...)

I can achieve this in Excel with:

=EOMONTH(TODAY(),-13)+1

(-12, -11, -10 etc)

(End of the current month plus a day (equalling the first day of the next month), minus 13 months = equalling 12 months prior to the start of the current month.

I've seen the Date.AddMonths, Date.StartOfMonth and the DateTime.LocalNow functions, however, I'm new to M and was hoping someone could provide the answer to this so I can understand the formatting of such a formula.

Thanks in advance for any responses.

Solution

  • You've got all the right pieces to put it together:

    Date.AddMonths(Date.StartOfMonth(DateTime.LocalNow()), -12)