Create a Date Dimension in Power BI in 4 Steps – Step 1: Calendar Columns

I have written multiple blog posts so far about creating a date dimension. However, I still get the question about how to create a date dimension. In this series of blog posts I  am going to explain in details how you can create a date dimension easily in Power BI (based on Power Query). this date dimension will be configurable with start and end date, will have fiscal calendar columns, and most importantly will have public holidays fetched live. In this first step we are creating the based for the date dimension for calendar dates. If you like to learn more about Power BI; read Power BI book from Rookie to Rock Star.

Introduction

I have written many years ago about how to create a date dimension in T-SQL, and after a while another post to create a date dimension in Power Query with M Script. I have also written another blog post about the importance of date dimension, and why it is critical to have a date dimension. In above posts, I just provided the script to create the date dimension, but I never explained the procedure. I feel the need that people would like to know how to create the date dimension. So, in this blog series I am going to explain in details from end to end; how to create a date dimension in Power BI using Power Query. at the final post I will provide the whole script to generate date dimension.

Why Power Query?

When it comes to create the date (or calendar) dimension in Power BI, there is always a question: Should I create the dimension with Power Query or DAX? This is a very good question to ask. It means that you know that there are multiple ways of creating it. What is the difference? the answer is that for many scenarios these are similar. So, it might not be different to use Power Query or DAX for it. However, there is a big difference.

Power Query can fetch data from live web APIs. This functionality gives you the power to fetch public holidays live from an API. You cannot do this with DAX! Apart from this big difference, majority of other requirements can be done with both, you can write calculations in both M or DAX to get calendar columns as well as fiscal columns. In many scenarios public holidays plays an important role in analyzing data. You would like to know how the sales was in holidays compared to other holidays and etc.

on the other hand side; some people refer to creating date dimension in DAX is easier. Well, that is not a true statement. You can easily copy the whole M script into a new Power Query blank query and that generates date dimension for you. as simple as copying DAX expression.

So, based on explanations above, I am going to create the date dimension in Power Query 🙂

Getting Started

For building this date dimension, you don’t need any based data set. We will build it from scratch. All you need is to know start year and end year for the date dimension. let’s start building the dimension;

Create a new Power BI Desktop file, start with Get Data -> Blank Query

Blank Query means query will return an empty string. Then you will be redirected to Query Editor window. We will use this query as a base for our date dimension.

Parameters

Let’s create parameters for Start Year and End Year. For creating every parameter, click on Manage Parameters, and then New Parameter.

Create one parameter for StartYear with data type of decimal, and default value of that you want as a start year

Then create another parameter for EndYear with same configuration. Now you should have both in list of queries;

You can always change value of parameters easily later.

First Step: Build the Base Query

Click on Query1 generated few steps ago. Then go to View Tab, and select Advanced Editor to see the M query in details;

Let’s generate the start date based on start year. and let’s consider first day of January as start date. this script will give you start date:

let
    StartDate = #date(StartYear,1,1)
in
    StartDate

Same thing for end date. however, end date would be 31st of December for the end year;

let
    StartDate = #date(StartYear,1,1),
    EndDate = #date(EndYear,12,31)
in
    EndDate

To generate the base query we need to know how many days exists between these two dates;

subtracting two dates from each other will return a Duration data type, then from Duration data type we can fetch number of days using Duration.Days Power Query function;

let
    StartDate = #date(StartYear,1,1),
    EndDate = #date(EndYear,12,31),
    NumberOfDays = Duration.Days( EndDate - StartDate )
in
    NumberOfDays

Now that we have number of days between two days, we can use a generator to create list of dates.

Date Generator

Generators are functions that returns a list. List.Dates is a function that returns a list of dates from start date for a number of occurrence based on a duration. here is syntax of using this function;

List.Dates(<start date>,<occurrence>,<duration>)

In our example, start date and occurrence is clear, duration would be #duration (1,0,0,0) meaning 1 day at a time. other parameters of duration data type are hour, minute, and second. so here is the change in query:

= List.Dates(StartDate, NumberOfDays+1, #duration(1,0,0,0))

The reason to add one to the number of days is that the difference is not including both dates. so adding one day will include the last date as well.

So far the query is as below;

let
    StartDate = #date(StartYear,1,1),
    EndDate = #date(EndYear,12,31),
    NumberOfDays = Duration.Days( EndDate - StartDate ),
    Dates = List.Dates(StartDate, NumberOfDays+1, #duration(1,0,0,0))
in
    Dates

result would be a list of dates;

Because the result is in a list format, and List in Power Query only can have one column, we need to convert it to table to be able to add extra columns to it. Converting to table is an easy transformation in List Tools -> Transform tab -> To Table. When converting to a table, you can choose delimiter and some configuration. leave these configurations as default and click on OK.

After converting it to table, rename the column to FullDateAlternateKey, and change data type of that to Date

Next Step: Adding Extra Calendar Columns

Congratulations! you have done the first step. first step was creating the base query. Now that we have a column of all dates, then it is easy to add calendar columns to it. Click on FullDateAlternateKey column and then from Add Columns Menu, under Date and Time Transformation, from Date section select Year

This simply add a Year column to the query:

Continue the process to add all calendar columns you want. More you add in this step will give more slicing and dicing power later in Power BI. for creating every column; select FullDateAlternateKey column first, then from Add Column tab, date transformations, select the column you want.

I would recommend all columns below to be added:

Month, Month Name, Quarter, Week of Year, Week of Month, Day, Day of Week, Day of Year, Day Name

Here is the final query so far:

Script

Here is the script of date dimension so far;

let
    StartDate = #date(StartYear,1,1),
    EndDate = #date(EndYear,12,31),
    NumberOfDays = Duration.Days( EndDate - StartDate ),
    Dates = List.Dates(StartDate, NumberOfDays+1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "FullDateAlternateKey"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"FullDateAlternateKey", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([FullDateAlternateKey]), type number),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([FullDateAlternateKey]), type number),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([FullDateAlternateKey]), type text),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([FullDateAlternateKey]), type number),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter", "Week of Year", each Date.WeekOfYear([FullDateAlternateKey]), type number),
    #"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([FullDateAlternateKey]), type number),
    #"Inserted Day" = Table.AddColumn(#"Inserted Week of Month", "Day", each Date.Day([FullDateAlternateKey]), type number),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day", "Day of Week", each Date.DayOfWeek([FullDateAlternateKey]), type number),
    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([FullDateAlternateKey]), type number),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Year", "Day Name", each Date.DayOfWeekName([FullDateAlternateKey]), type text)
in
    #"Inserted Day Name"

Summary

In this post you have seen how easy is to create a date dimension. The date dimension that we have created in this post is configurable via parameters. You can change parameters for start date and end date and refresh the query to see change in the result. This date dimension only had calendar columns, but in the next blog post I’ll explain how to add fiscal columns to it, and in the blog post after that I’ll explain how to add public holidays to it. Stay tuned for next posts.

Video

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 12 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, Power BI Summit, and Data Insight Summit.
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.