Returns a table with one column of dates calculated from the model automatically.
An integer from 1 to 12 representing the end month of fiscal year.
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.
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 include 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
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
Learn more about CALENDARAUTO in the following articles:
Other related functions are:
Contributors: Alberto Ferrari, Marco Russo
Microsoft documentation: https://docs.microsoft.com/en-us/dax/calendarauto-function-dax