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

» 2 related articles
» 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:

Related functions

Other related functions are:

Last update: Mar 13, 2024   » Contribute   » Show contributors

Contributors: Alberto Ferrari, Marco Russo, Kenneth Barber,

Microsoft documentation: https://docs.microsoft.com/en-us/dax/calendarauto-function-dax

2018-2024 © SQLBI. All rights are reserved. Information coming from Microsoft documentation is property of Microsoft Corp. » Contact us   » Privacy Policy & Cookies

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.

Iterator

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.

Volatile

A volatile function may return a different result every time you call it, even if you provide the same arguments. Click to read more.

Deprecated

This parameter is deprecated and its use is not recommended.

DirectQuery compatibility

Limitations are placed on DAX expressions allowed in measures and calculated columns.
The state below shows the DirectQuery compatibility of the DAX function.

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.


This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.