In my previous blog, I looked at the Date dimension tables which Power BI creates by default and why we shouldn’t use them in our analyses. In this blog, I will cover the requirements for customized Date tables and the different ways we can create them.

Just on a side note, I am going to use different names for my customized Date table, and here are some alternative terms which are usually used to name it:

  • Date dimension
  • Date table
  • Calendar table
  • Calendar dimension
  • Date dim

Date table requirements

 

Regardless of the way you are getting your Calendar table it should meet the following requirements:

  1. Your Calendar Table must contain all days and all years represented in your dataset
  2. Date column must be continuous. There should not be any missing dates
  3. There should not be BLANK values in the Date column
  4. Cannot contain duplicates of date or datetime values. Otherwise, the Date field wouldn’t be considered a unique list and can’t be used as a primary key for our table
  5. At least one field in the table should be of a date type
  6. If you import your Calendar table and it contains a date column containing both date and time you have to split the time component into separate column
  7. Mark your Calendar table as a Date table within your model

The last point is quite important. By marking your customized Date table, you let Power BI know that it should use this table for time-intelligent calculations. It is pretty straightforward to do that in Power BI Desktop.

Two ways of marking Date table: right-click on our date table and choose “Mark as Date table” or highlight the date table and under “Table Tools” select “Mark as date table” icon.

 

In both cases, you have to choose a column to be used for the date. It should be a date type and contain only unique values. In the drop-down menu Power BI will give you a selection of the dates columns that meet those requirements. By marking your customized Date table as a Date table you no longer use the default Date tables which we talked about in my previous blog. All the visuals based on those tables will break and Power BI warns you about it in its message.

 

How to create Date dimension

 

First of all, there is a big chance that your organization already has a date dim table. If you have your data source from SQL Server, Oracle, or MySQL the data warehouse probably contains the date dim. If it doesn’t  Script for Creating and Generating members for Date Dimensions: General Purpose – RADACAD here is the script for Microsoft SQL Server you can use to create Date Dim for general purposes.

There are many ways of creating a customized Date dimension available and I will give a brief overview of them. There are plenty of resources online if you want to dig deeper and get a better understanding of how it works.

 

Using DAX Functions

 

There are two DAX functions you can use to create a Calendar table.

CALENDAR() and CALENDARAUTO()

CALENDAR(StartDate, EndDate) function returns a table with one column of all dates between StartDate and EndDate.

You can assign a specific date range by using a DATE function

CALENDAR(DATE(2020,01,01), DATE(2020,12,31))

OR

You can use MIN and MAX functions to create a range of dates based on the Date column within your data model.

CALENDAR(

DATE(YEAR(MIN(Sales[Transaction Date])),1,1),

DATE(YEAR(MAX(Sales[Transaction Date])),12,31))

And after you’ve got your first date column you can start adding any columns you need for your analyses (Month, Month Name, Quarter, Week of Year, Week of Month, Day, Day of Week, Day of Year, Day Name, etc)

CALENDARAUTO() returns a table with one column of dates based on a fiscal year-end month. The range of dates is calculated automatically based on data in the model. And here lies one important issue with using this function. As you can see from the definition it uses the earliest date value from your model and expands until the latest date in your dataset. For many reasons our datset can contain some random date values (for example somebody has entered 1900 as a year when the value wasn’t known, or 2999 to show that it’s still in process). In this case, your date column will contain all the dates starting from 1900 and ending in 2999.

The syntax of the CALENDARAUTO() function is simple

CALENDARAUTO(FiscalYearEndMonth) where FiscalYearEndMonth is an integer from 1 to 12, which represents the last month of the Fiscal Year.

CALENDARAUTO(6) will give us dates for the Fiscal year starting in July.

Another approach is to use variables to define your MIN and MAX values within a date column and then return those using CALENDARAUTO()

Calendar Table =

VAR MinYear = YEAR(MIN(Sales[Transaction Date]))

VAR MaxYear = YEAR(MAN(Sales[Transaction Date]))

RETURN

FILTER(CALENDARAUTO(),

YEAR([ DATE ])>= MinYear &&

YEAR([ DATE ])<= ManYear)

As I have mentioned before there are plenty of resources and here All in One: Script to Create Calendar Table or Date Dimension using DAX in Power BI – RADACAD you can find the full script and step by step instructions on how to create a calendar table using DAX Functions.

 

Using M Power Query

 

The steps:

  1. Create a blank query from the “Get Data” icon
  2. Create two parameters for Start Year and End Year in “Manage Parameters” using decimal as a data type
  3. After you’ve done that open “Advanced Editor” and paste the script, which you can find here: Create a Date Dimension in Power BI in 4 Steps – Step 1: Calendar Columns – RADACAD
  4. Hit “Close & Apply” and you are done!

 

Compare the two:

 

Both ways of creating a Calendar table are pretty simple. You just need to find a code that satisfies your requirements and paste it into Power BI.

One of the benefits of creating Date table with DAX is that you are working in Power BI Desktop environment. On the other hand, if you have multiple files, you are creating Date table for each of them, which might increase the future maintenance of those tables.

The benefit of using M Power Query method is that you can create a dataflow entity using this date table, and re-use it in other models.

“The Date table created by a Power Query script can be easily used as a dataflow table. This means the date table, then can be re-useable to anyone who has access to that workspace and the dataflow. The re-usability is a big win when you create the Date table in the dataflow.” (RADACAD Power BI Date or Calendar Table Best Method: DAX or Power Query? – RADACAD)

Another advantage of using M Power Query method is that Power Query can fetch data from live web APIs. And if you need, for example, public holidays data you will be able to fetch it live from an API.

The last thing, after you’ve created your Calendar table and marked it as a Date table do not forget to connect it to all related tables in the Model view.

 

 

The Data School
Author: The Data School