Welcome to Excel Jet blog post. In this tutorial, we will learn what is CALENDERAUTO DAX function and CALLEDER DAX function. Let’s get started
- CALENDARAUTO returns a table with a single column named “Date” that contain a contiguous set of dates. The range of dates is calculated automatically based on the data in the model
- CALENDARAUTO Syntax =CALENDARAUTO()
- CALENDAR returns a table with s single column named “Date” that contains a contiguous set of dates. The range of dates is from the specified start date to the specified end date
- CALENDAR Syntax =CALENDAR(start date, end date)
Note, the earliest date i.e 01/01/2019 is regarded as the Minimum Date while the latest date in the model is regarded as the Maximum Date.
Calendar table is very important to use DAX Time Intelligence functions such as SPLY, YTD etc
In the caption below, we want to automatically create a calendar table based on the series of transaction dates in the Date column
To create the CALENDARAUTO:
In the Calculation group of the Modelling tab, click on New Table
In the formula bar, execute the DAX formula: Date = CALENDARAUTO()
Note, the CALENDARAUTO required to argument if user intends to fetch all the unique dates based on the Date column in the transaction table
After, we click Enter, from the caption above, we can see that all the dates are automatically generated in the New Table. However, we noticed that Date/Time Data Type is applied.
To remove the Time, in the Formatting group of the Modelling tab, click on Data Type: Date/Time drop down and select Date
From the caption above, we have all the dates automatically generated.
CALENDER DAX Function I
Next, we want to see the first method of how to create CALENDAR table. Note, both methods returns the same result
To create the CALENDAR Date Table:
In the Calculation group of the Modelling tab, click on New Table
In the formula bar, execute the DAX formula: Date = CALENDAR(DATE(2013,1,1),DATE(2017,12,31))
From the formula bar of the caption above, I nested DATE DAX function and provided the year, month and day arguments for both start date and end date inside the CALENDAR. While using the approach, we are required to know the Minimum and the Maximum Date Years in the fact table Date Column
CALENDER DAX Function II
Finally, we want to learn the second approach which is what I really use
To create the CALENDAR Date Table:
In the Calculation group of the Modelling tab, click on New Table
In the formula bar, execute the DAX formula: Date2 = CALENDAR(DATE(YEAR(MIN(Transaction_facttbl[Date])),1,1),DATE(YEAR(MAX(Transaction_facttbl[Date])),12,31))
From the caption above, we used Date, Year and MIN DAX functions based on the Date column in the fact table to return the start date. we used Date, Year and MAX DAX functions based on the Date column in the fact table to return the end date. This method does not required use to know the Minimum and the Maximum Date Years in the fact table Date Column.