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

0
votes

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.

2
datetimepowerquerym

2 Answers

0
votes

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

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

Many ways to do this to populate the rows.

You can

  • create a list of the dates
  • Convert that list to a table column
let
    Dates = Table.FromList(    
        List.Generate(
            ()=> [Month Start = Date.StartOfMonth(Date.AddMonths(DateTime.LocalNow(),-12)), idx = 11],
            each [idx] >= 0,
            each [Month Start = Date.StartOfMonth(Date.AddMonths(DateTime.LocalNow(),-[idx])), idx = [idx]-1],
            each [Month Start]), 

            Splitter.SplitByNothing(),{"Month Start"}),
    dateType = Table.TransformColumnTypes(Dates,{"Month Start", Date.Type})
in
    dateType