How to create a list of Months in power bi

2
votes

I need to create an dynamic list of months from current month back to 10 months ago using Power Query M. I found that there is only List.Dates to create a list of date between specific duration but no function help to create the months list. Could anybody has some way to help?

Thanks

2
powerbipowerquery

2 Answers

3
votes

Not sure what you are looking for, but the following code will give you 3 columns for the previous 10 months and the current month (at last refresh, you need to refresh each month):

Start of Month (date)

End of Month (date)

Name of Month in Vietnamese (text)

let
    Source = Table.FromList({-10..0}, each{_}),
    AddedStartOfMonth = Table.AddColumn(Source, "StartOfMonth", each Date.StartOfMonth(Date.AddMonths(Date.From(DateTime.LocalNow()),[Column1])), type date),
    AddedEndOfMonth = Table.AddColumn(AddedStartOfMonth, "EndOfMonth", each Date.EndOfMonth([StartOfMonth]), type date),
    AddedNameOfMonthInVietnamese = Table.AddColumn(AddedEndOfMonth, "MonthName", each Date.MonthName([StartOfMonth],"vi-VN"), type text),
    RemovedColumn = Table.RemoveColumns(AddedNameOfMonthInVietnamese,{"Column1"})
in
    RemovedColumn
1
votes

Assuming you want to achieve a filter, I would just hit the Filter drop-down button for your date column in the Query Editor, then choose Date/Time Filters / In the Previous / 10 / months. This will generate something like this:

Filtered Rows = Table.SelectRows(Source, each Date.IsInPreviousNMonths([My Date], 10))