cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
igaca
Helper III
Helper III

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 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

5 REPLIES 5
Eric_Zhang
Microsoft
Microsoft

@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.

 

 

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

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
Microsoft
Microsoft

@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

 

Great DAX patterns, thank you for that!

 

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

 

Igor

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.