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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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