Power BI Date Dimension Demystified Part 1

Hello All,

Today let us take the first step towards demystifying Date Table journey. Date Dimension plays a very important role. Almost always there is a need to perform and derive time-based insights. Few ground rules must be adhered to when it comes to creating a robust date table. These are

  • The date table must contain unique values
  • The date table must contain no null values
  • The date table must contain contiguous date values (from beginning to end)

This series of articles will go over 10 different manipulations/iterations of how one can achieve this. We will also incorporate fiscal year and custom sort

As I see, we have two tools at our disposal for these date tables. Using DAX and using M Query.  

  1. Calendarauto DAX function
  2. Calendar DAX function
  3. Number.From M query
  4. List M query
  5. Parameters
  6. Dynamic Parameters
  7.  Invoking a function
  8. Sort based on the calendar month 
  9. Sort based on Fiscal year — approach 1
  10. Sort based on Fiscal year — approach 2

For any date table, we must have 3 values/properties present. The start date, the end date, and the duration. For the purposes of these articles, let us call them, 3P as we will be revisiting them again

The start date — This the first (earliest) date available. We can either enter this manually (using calendar() function — coming up in the next article) or it is calculated based on the model. 

The end date — This is the last (latest) date available. We can either enter this manually (using calendar() function — coming up in the next article) or it is calculated based on the model. 

The duration — This is the increment or step. Almost always this increment is “day”. For example, let us consider the month of June 2020. 

The start date would be 06/01/2020 — 1st of June 2020

The last date would be 06/30/2020 — 30th of June 2020

The duration would be each day. Therefore if we were to create a date-table we should have 30 unique dates for the month of June 2020. 

Let us begin with the CALENDARAUTO() DAX function. 

This function returns a table with a single column named “Date” that contains a contiguous set of dates. The range of dates is calculated automatically based on data in the model. 

This function has some considerations/rules associated with it, they are :

  1. The model MUST contain date-time values. 
  2. It does not take into account the date values based on the calculated table and calculated column.
  3. The earliest date is calculated based on the MinDate 
  4. The latest date is calculated based on the MaxDate 
  5. If we pass an argument (an integer between 1 and 12) then our date table range “shifts” by that step. 

Let us begin with our sample data. 

My other post (Power Query) talks about how to connect to this data. I will go over very quickly over this once again. But if you need to revisit Power Query again, click the icon below to do so.

  1. Click get data
  2. Click on Excel
  3. Navigate to the folder where the excel file resides
  4. Load the data (usually we would go with transform data as this is a good practice to ensure the quality is consistent. However this sample data is the right format as we need it to be in). 
  5. Navigate to modeling tab and click on the new table
  6. This should open the formula bar
  7. Name the table (I named it date table from calendarauto DAX and type in calendarauto(). My final formula is date table from calendarauto DAX = CALENDARAUTO() )
  8. This will create a date-table with a single column called date as seen below
  9. As you can see it took the MinDate, MaxDate, and duration automatically based on the rules above. 
  10. We did not need to pass any parameter as by default the parameter (fiscal year-end month) is 12 (December)

Now let us supply 3 as the argument to our function and see the “shift” in action. 

  1. Navigate to modeling tab and click on the new table
  2. This should open the formula bar
  3. Name the table (I named it date table from calendarauto with 3 and type in calendarauto(3). My final formula is the date from calendar auto with 3 = CALENDARAUTO(3) )
  4. This will create a date-table with a single column called date as seen below
  5. As you can see it took the MinDate, MaxDate, and duration automatically based on the rules above. 
  6. We passed 3 as a parameter and hence the MinDate became the end of 3rd month for the year before. Our original date started from 1/1/1995 and ended on 12/31/2025, by providing 3 as the parameter we shifted the start date. As per our rule that we discussed earlier, the MinDate is calculated based on the fiscal year-end month. 
  7. The fiscal year-end month is nothing but the parameter that we provide. Since by default the parameter (fiscal year-end month) is 12 (December) hence the MinDate was 1/1/1995. In this case, we provided 3 as the parameter so our start date should become 4/1/1994 and keeping the reference intact the end date is the end of March i.e. 3/31/2026

Our work here is not done yet. We have just a single column with date-time values. However, this is by far the most important piece if creating the entire date table. Once we have unique, non-null, date-time values (remember our rules above) we are able to extract all the other information out of it.

However, this is all we need to extract all the other information out of it. The steps to extract the various values such as month name, month number, day number, etc will be covered in the next post when we demystify the calender() function since extracting the information is exactly the same for both these approaches. 

I hope that I was able to demystify this for you. However, if you have any concerns/queries/doubts or feel that I have made an error about this please do not hesitate to leave a comment or send me the feedback. I will be more than happy to discuss this. 

Published by Ansh Purohit

I am a data enthusiast and enjoy working with data manipulation tools and languages. Power BI is my passion and I consider myself a life long learner. I enjoy sharing my knowledge with others because I firmly believe that Knowledge Increases by Sharing.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: