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 )
