CALENDARAUTO DAX Function in Power BI
The CALENDARAUTO DAX function in Power BI returns a table with a single column named "Date" that contains a continuous range of dates.
What it Does:
- The range is determined automatically from the minimum and maximum dates across all date/time columns in the data model.
- It expands the date range to cover full years, based on the fiscal year end month.
DAX Syntax CALENDARAUTO(fiscal_year_end_month)
The fiscal_year_end_month is an optional parameter. An integer from 1 (January) to 12 (December) that defines which month is considered the end of the fiscal year. Its default value is 12 (December) means the fiscal year ends in December.
Example: Basic Calendar.
DAX
Calendar Auto = CALENDARAUTO()
Creates a date table starting from Jan 1 of the earliest year to Dec 31 of the latest year found in the model.
Example: Calendar with Fiscal Year Ending in March.
DAX
Calendar Auto Fiscal = CALENDARAUTO(3)
If fiscal year ends in March:
- Start date will be April 1 of the earliest year.
- End date will be March 31 of the latest year.
Difference: CALENDAR vs CALENDARAUTO
| Function | Behavior |
|---|---|
| CALENDAR | Requires explicit start & end dates. |
| CALENDARAUTO | Auto-detects min & max dates from your model, expands to full years. |