DAX CALENDAR & CALENDARAUTO
In this blog, we’ll continue looking at more DAX ‘bear necessities’.
We focus on the DAX calendar functions namely CALENDAR and CALENDARAUTO.
We will explain how these functions work and how you can use them to create date tables and therefore time intelligence reporting within Power BI.
If you prefer to watch the YouTube video, rather than reading the blog, please see the video below:
THE IMPORTANCE OF DAX DATE FUNCTIONS
Let’s explore why date functions are important and for what we need them. We need date tables in Power BI if we want to do time intelligence reporting, where time intelligence reporting may be as follow:
- month to date sales values
- quarter to date sales values
- year to date sales values, and
- if we want to compare a period of this year to the same period of last year
RULES FOR WORKING WITH DATE FUNCTIONS
The following if I can call it ‘rules’ are useful to follow when working with date tables:
- all dates should be present, meaning it should be dates for which you have data
- all days should be sequential, there must not be gaps in your data, time intelligence will not work in Power BI if there are gaps
- you can specify holidays, if you, for instance, want to calculate the number of working days in the year, then we will exclude public holidays and weekends
DAX CALENDAR FUNCTION
We will start off with exploring the CALENDAR date function. The CALENDAR function returns a table with a single column called date and that date column contains a continuous set of dates for the given range.
In other words, if you look at the syntax you can see that you have to specify the start and end date and it returns a table with one column of all dates between the specified start date and end date.
Let’s use an example to explore how this works. In the Modeling tab, we select a new table and call our function ‘Dates’. We select our CALENDAR function and in the CALENDAR function, we use the DATE function to specify the year. We will use the start date as the first day of the first month in 2015 and the last day of the last month in 2015 as the end date.
Dates = CALENDAR(
Let’s see the table that we have created.
Our table ‘Dates’ returned one column with the start date the 2015/01/01 and the last value is 2015/12/31, exactly as we specified in our function. This is very useful, but we want to be able to add more information to our calendar table.
Let’s add new columns namely ‘MonthName’ (specifying the name of the month), ‘MonthYear’ (specifying the month of the year) and ‘Year’ (specifying the year).
The DAX for ‘MonthName’:
MonthName = FORMAT(
The DAX for ‘MonthYear’:
MonthYear = FORMAT(
The DAX for ‘Year’:
MonthYear = YEAR(
Let’s see our table output.
Another use case is when we would like to have the date range of the minimum and maximum value of a certain table that can dynamically update. Let’s see how this can be done. Let’s create a new table and call it ‘Dates 2’. We use the CALENDAR function, but we are going to say give us the minimum value in our ‘Sales’ table form the ‘DOCDATE’ column and the maximum date from the ‘Sales’ table of the ‘DOCDATE’ column.
Dates 2 = CALENDAR(
Let’s see our output.
What happened is that it has given us a result where it returned the minimum and maximum dates from the ‘DOCDATE’ column and it returned all the dates in between as well. That’s very useful as it can dynamically update our calendar table as we need it.
Let’s expand on this formula to see what else we can do. In our ‘Sales’ table, we have 2 date columns namely the one we used ‘DOCDATE’ and a ‘SHPDATE’. Let’s determine the minimum value across the 2 columns and the maximum value across the 2 columns.
Dates 2 = CALENDAR(
From the formula, it is clear that we are going to calculate and return the date that is the minimum date of the two columns and the maximum date of the two columns.
If for example, the shipping date column was from another table it would also be able to assess that, so this is really useful if you want to make sure that your time intelligence function includes all the dates that are required for your calculations.
The last function I want to look at is CALENDARAUTO. CALENDARAUTO automatically creates a date calendar table based on your data model or optionally you can also specify the starting month, which is useful for fiscal years. This function uses all the data in your data model excluding calculated columns and tables. So CALENDARAUTO goes and looks at every single table in your data model to evaluate the minimum and the maximum dates and it returns a calendar column or table with the range that includes all of those days.
Now the problem with this is that while this is useful in certain situations it may not be the function to use for everybody. The reason is if you had a customer’s table and you have a customer birthdate and that customer was born in 1920 or 1930 then your calendar table is going to include dates from the 1930s all the way through the current date. So just be careful when you use this function.
Let’s create this function and see how it works. We create a new table called ‘Calendar Auto’. The function CALENDARAUTO only asks for the fiscal year end month. So, if your fiscal year ends in the 7th month then you just type 7.
Calendar Auto = CALENDARAUTO(
The result is a calendar starting from the eighth month (August) and it will continue to do so and retrieves all dates from the data model.
That is it for this blog post for the CALENDAR and CALENDARAUTO function, it is very useful but choose wisely when using these the functions.