CALENDARAUTO Function (DAX)

How does the CALENDARAUTO function (DAX) work?

The CALENDARAUTO function (DAX) returns a table with one column named “Date” which contains a contiguous set of dates. The range of dates is calculated from the model automatically. 

CALENDARAUTO Formula Syntax

CALENDARAUTO(
     [fiscal_year_end_month]
)

How do you use the CALENDARAUTO function?

Use the CALENDARAUTO function if you want to search among all the dates in the data model and you want to automatically find the first and last year referenced in your model. 

It will search the date table from the first day of the fiscal year based on the earliest date in your date column, all the way up to the last day of the fiscal year of the latest date.  

Related Blog Posts

Related Support Forum Posts

Comparing New vs Lost vs Steady Customers over Multiple Years – Customer Churn Examples – Advanced DAX
Dynamic Date Table (Update Date Table)
Why is my running total showing values after specific date?

Considerations when using the CALENDARAUTO function

CALENDARAUTO does not use calculated tables and calculated columns when searching for date columns. It analyzes only the imported columns when searching for date columns. 

When calculating the date range using the CALENDARAUTO function, the earliest date is retrieved using the MinDate while the latest date is taken using the MaxDate. An error will come up if the data model does not have datetime values which are not in calculated columns or tables. 

Related Video Tutorials

Formula examples using the CALENDARAUTO function

FILTER ( CALENDARAUTO ( 3 ), YEAR ( [Date] ) >= 2015 )

= CALCULATE( [TotalSales], SAMEPERIODLASTYEAR( ‘CALENDARAUTO() DATE'[Date] ) )

GENERATE ( CALENDARAUTO(), VAR currentDay = [Date] VAR day = DAY( currentDay ) VAR month = MONTH ( currentDay ) VAR year = YEAR ( currentDay )

Related Course Modules

Ultimate-Beginners-Guide-To-PowerBI
Center of Excellence

DOWNLOAD


Download our comprehensive DAX Formula Reference Guide as perfect companion as you learn how to use DAX formulas within Power BI.


Download DAX Formulas Reference Guide
Download DAX Formula Reference Guide
Membership