I have previously written about using Power Query for creating calendar tables, However, some people still love to do that in DAX. There are a couple of DAX functions which makes the process of creating a calendar table very simple and easy; Calendar() and CalendarAuto(). In this short blog post, I’ll explain what are these functions, and how you can use them to create a calendar table in just a few seconds. If you like to learn more about Power BI, read Power BI book from Rookie to Rock Star.
Good reads related to this article
I have written about Date dimension in Power BI many times, I advise you to have a good read on articles below;
Power BI Date Dimension: Default or Custom? Is it confusing?
Creating Date Dimension in Power Query Part 1: Calendar Columns
Creating Date Dimension in Power Query: Part 2: Fiscal Columns
Creating Date Dimension in Power Query: Part 3: Public Holidays
so I believe I have written almost everything about date dimension, except how to create it in DAX, let’s check it out.
Prerequisite
The dataset for this model is the AdventureWorksDW2012 Excel file, which you can download from here. Tables used in this example are; DimCustomer and FactInternetSales.
Calendar() Function in DAX
One of the easiest ways of creating a calendar table is using Calendar() function in DAX. The Calendar function is a very simple function with just two input parameters; start date, and end date.
<table output>=Calendar(<start date>, <end date>)
The output of the Calendar function is a table with one column which includes all dates between the start and end date, with one day at each row. Here is an example of creating a calendar table using this function:
Create a new Table. (The output of the Calendar function is a table)
Then write the expression below;
Calendar = CALENDAR(DATE(2018,1,1),DATE(2019,12,31))
The two inputs here are two date fields, that is why I used Date functions to generate them from the year, month, and date. You can always use Date() functions in this way:
Date(year, month, day)
The output of this calculation is a table with one column and values starting from 1st of Jan 2018, and end at 31st of Dec 2019. This is how easy is to use the Date table;
It is not mandatory to put static dates when you define the calendar table, you can even use it with dates relative to the current date. here is another example of creating a calendar table with all dates from a year before the current date, to a year after;
Calendar Relative = CALENDAR(
TODAY()-365,
TODAY()+365
)
Or you can even create it based on a column, and start with the minimum date in that column and end with maximum date, like the below expression;
But wait! instead of doing it this way, there is a better function for it; CalendarAuto()
CalendarAuto() DAX Function
CalendarAuto() function also produces a table with a column of dates, one day at a time. However, you don’t set the start and end time. The start and end time would be based on the minimum date value in your data model (Across all columns, except calculated columns, tables, and measure), and maximum date value in your data model (Across all columns, except calculated columns, tables, and measures). This is how you use the CalendarAuto function;
<table output>= CalendarAuto([<fiscal year’s start month number>])
The fiscal year’s start month is an optional parameter. If you don’t set it, it will use the calendar year instead and starts in January and ends in December. Here is an example of using it:
CalendarAuto = CALENDARAUTO()
As you can see, the date values are starting from 1st of January 1910. The reason is that somewhere in our data model, there is a date field, which has a value in 1910. It might not be first of January of that year, however, but because the CalendarAuto function always starts at the first day of the year (or fiscal year), and ends at the last day of the year (or fiscal year), it started then from 1st of January.
Now If I want to do it as a fiscal calendar considering that 1st of July is the first day of a fiscal year, this is how I can use it: (Note that you should enter the last month of the fiscal year, in this example: 6, saying June is the last month)
Is it better to use Calendar or CalendarAuto functions in DAX, or build the date dimension using Power Query?
This is always the question that I get; where should I build my date dimension? My answer is always one thing: Wherever you feel more comfortable with it! You have to maintain this dimension table going forward, so you have to decide where you are going to build it. there are pros and cons of building it in everywhere, for example;
- Calendar() and CalendarAuto() functions are very simple to use, and fast way of creating a date table.
- If you create a date table using Power Query, then you can query live APIs and web services and fetch public holidays and other information live.
- If you build your date table in the data source, such as the SQL Server database, you can then use it across multiple visualization tools.
Do I really Need a date dimension?
Yes, Definitely you do. I wrote a whole article about why do you need a date dimension, read it here.
Isn’t there an easier way to create a Date Dimension? Why Power BI don’t have a date dimension?
There is a built-in dimension for Power BI, and I explained in another article full in details how to use it and what is the difference of that with a custom dimension, read it here.
Summary
Calendar and CalendarAuto functions are useful and very simple-to-use functions in DAX. You can create a date/calendar table with one of these functions in just a few seconds. However, I strongly recommend reading more information about date dimension in my other articles:
Power BI Date Dimension: Default or Custom? Is it confusing?
Creating Date Dimension in Power Query Part 1: Calendar Columns
Creating Date Dimension in Power Query: Part 2: Fiscal Columns
Creating Date Dimension in Power Query: Part 3: Public Holidays
Video
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.