Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

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

@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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.