Power BI: CALENDARAUTO & Two Ways To Create CALENDAR DAX Formula

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.

Posted in Blog and tagged , , , .

Leave a Reply

Your email address will not be published. Required fields are marked *