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

[igaca]

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 generate a line of code which I can just copy & paste into future models to automatically generate calendar tables with all desired levels of granularity.  

I've used "List.Dates" or simply generated a list with date numeric values (e.g. {40544..41857}....and even this after utilizing CALENDARAUTO() in DAX to ascertain the Calendar table boundaries) but it would be great to eliminate that initial step and simply have a function do it for me.

If anyone is familiar with a solution, please let me know.

Thanks in advance!

Igor

[Eric_Zhang]

@igaca

Do you mean replicate a calender table as above or an equvalent to CALENDARAUTO? For former it would be not to duplicated. For latter

Again

According to the BOL, CALENDARAUTO returns a table with a single column named “Date” that contains a contiguous set of dates. The range of dates is calculated automatically based on data in the model. Based on my test, in the model means it would detect the date low and high year boundaries in all tables. Though I'm not familar with Power query, I'd doubt there's an equivalent in Power query.

[igaca]

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]

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)),
.....

[Eric_Zhang]

@igaca

According to the BOL, CALENDARAUTO returns a table with a single column named “Date” that contains a contiguous set of dates. The range of dates is calculated automatically based on data in the model. Based on my test, in the model means it would detect the date low and high year boundaries in all tables. Though I'm not familar with Power query, I'd doubt there's an equivalent in Power query.

For copy and paste purpose, you can use a DAX formula as well. Check

DimDate =
VAR fiscal_year_end_month = 3
RETURN
    ADDCOLUMNS (
        CALENDARAUTO ( fiscal_year_end_month ),
        "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
        "Year", YEAR ( [Date] ),
        "Fiscal Year", IF (
            MONTH ( [DATE] ) <= fiscal_year_end_month,
            YEAR ( [DATE] ) - 1,
            YEAR ( [DATE] )
        ),
        "Quarter", "Q" & FORMAT ( [Date], "Q" ),
        "Fiscal Quarter", "Q"
            & FORMAT (
                IF (
                    fiscal_year_end_month < MONTH ( [Date] ),
                    DATE ( YEAR ( [Date] ), MONTH ( [Date] ) - fiscal_year_end_month, 1 ),
                    DATE ( YEAR ( [Date] ) - 1, MONTH ( [Date] ) + 12 - fiscal_year_end_month, 1 )
                ),
                "Q"
            ),
        "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q"
            & FORMAT ( [Date], "Q" ),
        "Fiscal YearQuarter", IF (
            MONTH ( [DATE] ) <= fiscal_year_end_month,
            YEAR ( [DATE] ) - 1,
            YEAR ( [DATE] )
        )
            & "/Q"
            & FORMAT (
                IF (
                    fiscal_year_end_month < MONTH ( [Date] ),
                    DATE ( YEAR ( [Date] ), MONTH ( [Date] ) - fiscal_year_end_month, 1 ),
                    DATE ( YEAR ( [Date] ) - 1, MONTH ( [Date] ) + 12 - fiscal_year_end_month, 1 )
                ),
                "Q"
            ),
        "Monthnumber", FORMAT ( [Date], "MM" ),
        "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
        "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
        "MonthNameShort", FORMAT ( [Date], "mmm" ),
        "MonthNameLong", FORMAT ( [Date], "mmmm" ),
        "DayOfWeekNumber", WEEKDAY ( [Date] ),
        "DayOfWeek", FORMAT ( [Date], "dddd" ),
        "DayOfWeekShort", FORMAT ( [Date], "dddd" )
    )

Change the 3 to your real fiscal end month accordingly.

VAR fiscal_year_end_month = 3

[igaca]

Great DAX patterns, thank you for that!

If someone knows how to replicate in Power Query, please share.  Thanks in advance!

Igor