CALENDARAUTO DAX Function (Date and Time)
Returns a table with one column of dates calculated from the model automatically.
Syntax
| Parameter | Attributes | Description |
|---|---|---|
| FiscalYearEndMonth | Optional |
An integer from 1 to 12 representing the end month of fiscal year. |
Return values
Returns a table with a single column named “Date” containing a contiguous set of dates. The range of dates is calculated automatically based on data in the model.
Remarks
By default, the fiscal year ends in December (month 12).
CALENDARAUTO ignores calculated tables and calculated columns searching for date columns. Only the imported columns are analyzed to search for date columns.
Internally, CALENDARAUTO calls CALENDAR providing a date range that includes all the days in the range of years referenced by data in the model, according to the following rules:
- The earliest date in the model which is not in a calculated column or calculated table is taken as the MinDate.
- The latest date in the model which is not in a calculated column or calculated table is taken as the MaxDate.
- The date range returned is dates between the beginning of the fiscal year associated with MinDate and the end of the fiscal year associated with MaxDate.
An error is returned if the model does not contain any datetime values which are not in calculated columns or calculated tables.
» 1 related function
Examples
In this example, the MinDate and MaxDate in the data model are July 1, 2010 and June 30, 2019.
CALENDARAUTO ( ) will return all dates between January 1, 2010 and December 31, 2019.
CALENDARAUTO ( 3 ) will return all dates between April 1, 2010 and March 31, 2020.
The following expression will return all dates between January 1, 2015 and March 31, 2020.
FILTER (
CALENDARAUTO ( 3 ),
YEAR ( [Date] ) >= 2015
)
The following expression creates a simple calculated date table.
ADDCOLUMNS (
CALENDARAUTO (),
"Year", YEAR ( [Date] ),
"Quarter", "Q" & QUARTER ( [Date] ),
"Month", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] )
)
ORDER BY [Date] ASC
Related articles
Learn more about CALENDARAUTO in the following articles:
-
Creating a simple date table in DAX
This article shows how to build a basic date table using a calculated table and DAX. » Read more
-
Creating a simpler and chart-friendly Date table in Power BI
A Date table in Power BI can have a smaller number of columns by leveraging custom format strings to adequately control the chart visualization and the sort order. » Read more
Related functions
Other related functions are:
Last update: Nov 14, 2024 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo, Kenneth Barber,
Microsoft documentation: https://docs.microsoft.com/en-us/dax/calendarauto-function-dax