Create dynamic Calendar based on todays date (M-query)

[Anonymous]

Hi 

Just in case someone else needs to create a calendar that is x months before current date and x months after, her is M-query code to create Calendar table.

Create Blank Query and open Advanced Query - insert the code below instead of the default code.

You can change the StartDate and EndDate as you please, here it is 1 month before and 12 month after:

let
Source = List.Dates(StartDate, DurationDays, #duration(1, 0, 0, 0)),
StartDate = DateTime.Date(Date.StartOfMonth(Date.AddMonths(DateTime.LocalNow(),-1))),
EndDate = DateTime.Date(Date.EndOfMonth(Date.AddMonths(DateTime.LocalNow(),12))),
DurationDays = Duration.Days(EndDate - StartDate),
ToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(ToTable,{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}})
in
#"Changed Type"

[v-piga-msft]

Hi @Anonymous ,

Is this a sharing or a question?

If this is a question, please share your desired output so that we could help further on it.

Best Regards,

Cherry

[Anonymous · Accepted solution]

Hi

It is only for sharing.  I have tagged it as such.

rgds

Kirsten