Creating Calendar Table in Power BI using DAX Functions

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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;

Do You Need a Date Dimension?

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;

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;

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:

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.

2017-01-13_12h49_19

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:

Do You Need a Date Dimension?

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

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

Leave a Reply

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