CALENDARAUTO DAX Function (Date and Time)

Returns a table with one column of dates calculated from the model automatically.

Syntax

CALENDARAUTO ( [<FiscalYearEndMonth>] )
Parameter Attributes Description
FiscalYearEndMonth Optional

An integer from 1 to 12 representing the end month of fiscal year.

Return values

Table A table with a single column.

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).

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.

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
)

Related functions

Other related functions are:

Last update: Oct 13, 2018   » Contribute   » Show contributors

Contributors: Alberto Ferrari, Marco Russo, Shenzq, Daniil Maslyuk

MSDN documentation: https://msdn.microsoft.com/en-us/query-bi/dax/calendarauto-function-dax

DAX Conventions
Context Transition

This function performs a Context Transition if called in a Row Context. Click to read more.

Row Context

This expression is executed in a Row Context. Click to read more.

Not recommended

The use of this function is not recommended. See Remarks and Related functions for alternatives.

Not recommended

The use of this parameter is not recommended.

Deprecated

This function is deprecated. Jump to the Alternatives section to see the function to use.

Deprecated

This parameter is deprecated and its use is not recommended.

Contribute

Want to improve the content of CALENDARAUTO? Did you find any issue? Please, report it us!
All submissions will be evaluated for possible updates of the content.