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 different manipulations/iterations of how one can achieve this.

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. 



Categories: Date Dimension

Tags: , , , ,

Leave a Reply

Thank you for subscribing to my blog

There was an error while trying to send your request. Please try again.

Datum to Data will use the information you provide on this form to be in touch with you and to provide updates and marketing.
%d bloggers like this: