Forum Discussion

igaca's avatar
igaca
Helper III
10 years ago

Power Query /M version of CALENDARAUTO() DAX function?

Am curious if there is a comparable function in M which looks at the date fields in the available queries and generates a contiguous date table from first to last date value?   I am wanting to gene...
Need Help
Tutorial Requests
igaca's avatar
igaca
Helper III
10 years ago

I was looking for an equivalent to DAX CALENDARAUTO() in M.  If someone has managed to work out a solution, an input would be greatly appreciate.  Meanwhile, current setup I utilize is not a bad way to go either.

 

Thank you,

 

Igor

delswingle's avatar
delswingle
Regular Visitor
9 years ago

Igor,

 

I was trying to tackle a similar problem the other day. I created a function to find the MIN date which I call when creating the date table. I used the current time as it better suited my needs but you could also created a second function to find the MAX to find both ends of your date spectrum of a specified table/column.

 

***Function Start Date***
(StartDate) =>
let
Source = Sales,
StartDate = Table.Group(Source, {}, {{"MinDate", each List.Min([Order Date Key]), type datetime}})[MinDate]{0}
in
StartDate

 

 

***Start of Date Table*** 

let
Start = Number.From(StartDate(null)),
End = Number.From(DateTime.LocalNow()),
Source = Date(StartDate(null), End - Start, #duration(1, 0, 0, 0)),
.....