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

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

2017-08-28_14h47_30

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

2017-08-26_10h29_24

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.

2017-08-28_13h33_28

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

2017-08-28_13h35_03

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

2017-08-28_13h36_58

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;

2017-08-26_10h35_24

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:

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

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;

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:

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;

result would be a list of dates;

2017-08-28_13h59_55

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.

2017-08-28_14h02_19

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

2017-08-28_14h04_25

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

2017-08-28_14h08_49

This simply add a Year column to the query:

2017-08-28_14h11_45

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.

2017-08-28_14h16_57

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:

2017-08-28_14h15_25

Script

Here is the script of date dimension so far;

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.

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">