CALENDARAUTO function is a Power BI Date and Time Function in DAX which returns table with single column which contains a contiguous set of dates.
- The range of dates is calculated automatically based on data in the model.
- Column that contains the date is named as Date.
- The date range returned by CALENDARAUTO function is dates between the beginning of the fiscal year associated with Min Date and the end of the fiscal year associated with Max Date.
- CALENDARAUTO ignores calculated tables and calculated columns searching for date columns. Only the data in model are used to search for date columns.
- CALENDARAUTO function returns an error, if the model does not contain any datetime values which are not in calculated columns or calculated tables.
SYNTAX
CALENDARAUTO([fiscal_year_end_month])
fiscal_year_end_month an integer value from 1 to 12 that represent the end month of fiscal year. It is an optional.
Here we have a “Global Super Store” data model, and you can see there are two date columns “Order Date” and “Ship Date”
Lets take a look on Max and Min dates for “Order Date” and “Ship Date” columns that is returned by Summarzie table.
You can see that that Min and Max Order Date is 1st Jan 2011 and 31st Dec 2014 while the Min and Max Ship Date is 3rd Jan 2011 and 7th Jan 2015.
There are many ways to check max and min dates in dataset, you can directly go to the data model and sort the required dates column in ascending and descending order to see the Max and Min dates, or you can create a separate measure to get the Max and Min dates also you can quickly check by creating a summarize table.
For better understanding, here have created a summarize table that returns the max and min dates so that you can quickly see all dates together and compare.
Using CALENDARAUTO function for generating dates
As you know, CALENDARAUTO generates the single column dates and the range for date will be generated based on a min and max dates present in the data model.
If you compare for Min date between Order date and Ship Date columns then it is 1st Jan 2011 while Max date between Order date and Ship Date columns is 7th Jan 2015.
So CALENDARAUTO function generates the dates starting from the 1st Jan 2011 fiscal year associated with Min Date is 2011 to 31st Dec 2015 as fiscal year associated with Max Date is 2015.
Following DAX CalendarTable uses CALENDARAUTO function which generates the calendar table.
CalendarTable = CALENDARAUTO()
Once you commit the DAX, it creates a new table named CalendarTable in data model which returns the dates. As you can see, a calendar table named CalendarTable is created.
Lets check the dates generated by Calendar table. Go to Data view and you can see, the dates generated by CALENDARAUTO function DAX.
Also you will notice that the date range starting from 1st Jan 2011.
Lets check the Date range for Max date, Just sort the column order to descending order as shown below. And you can see the Max Date is 31st Dec 2015.
You can also generates a date range based on providing a value for fiscal year end month in CALENDARAUTO function.
Lets provide a fascial year end month value 1 to CALENDARAUTO function.
CalendarTable = CALENDARAUTO(1)
You can see now this time the date range is started from 1st Feb 2010.
Lets check for Max date range, and the Max Date range is 31st Jan 2015.
CALENDARAUTO Function returns an error when Data model does not contain any date time column
Lets delete the Order Date and Ship date columns from Data Model, then try to use CALENDARAUTO Functions for generating a date range.
As you can the data model does not have any date columns now.
Lets see what happens when you try to use CALENDARAUTO Function.
CalendarTable = CALENDARAUTO()
You can see, it gives an error. So it is must to have date columns in data model to working with CALENDARAUTO function.
Also Read
