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

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:

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;

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;

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 on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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.

26 thoughts on “Create a Date Dimension in Power BI in 4 Steps – Step 1: Calendar Columns

  • When creating a start year and year I have required fiscals in my company (SD = 4/1 and ED 3/31) which I have populated. When creating a Start of Year/End of Year/ Quarter, etc. It reverts to January. How can I ensure this corresponds to my Start Date and End Date?

    • Hi Mike
      If you want the whole date dimension to start from the date that you want (which is the start of the fiscal year). then set that as your start DATE. I have a part of the calculation that I take a year, and then calculate the first of Jan of that year as the start date. If you have a custom start date, then simply just use that instead of that process
      Cheers
      Reza

      • Hi Reza, thank you very much for these easy steps to create a date dimension. I need one more tip, I already have set my start date based on my company’s fiscal date, e.g 11/1/2016, which means year should be 2017, instead of calendar year, so how do I set in PBI that my year start on Nov 1st so it can understand the correct year and quarter order?

  • Reza, I am trying setting up right data model to work with Date dimension where this table is supposed to connect to two data sets – opportunity & revenue. In report, there is need to have two tiles showing top 5 accounts by opty & revenue but they need to update based on date level drop down like year, quarter and month. What i read so far, we can’t change active relationship for tables though it may work for calculating only measures. I am trying to solve situation where I need to apply a visual filter based on date filter on these two data sets. Any help is appreciated.

    • Hi.
      If I understand correctly, you have two tables: opportunity, and revenue. each have a date field. now what is the problem to connect both to the same date table? if you have a date table, you can connect both there, it would be active relationship, and it would work fine.
      Cheers
      Reza

  • Really useful article, was a huge help as i tried to get a date working the way i wanted and with fiscal dates as well.

    Thanks for giving this to everyone!

    Cheers
    John

  • Hello, what is the best way to create a date dimension once but use in multiple PBI reports? Create date dimension in Excel and use “Get Data” on each PBI?

    • I would suggest creating a dataflow for that.
      this is what I do, I have a workspace in Power BI service, which I keep my general tables such as date dimension as dataflows. and then I get data from it through Power BI Desktop.
      Cheers
      Reza

    • Hi
      The reason that Fiscal week never came out as an article is that the scenario is different in each business.
      some businesses consider the first Monday of the year as the start of their fiscal year.
      some other business, say the second Monday in April for example, etc.
      what is your fiscal week scenario like?
      Cheers
      Reza

  • Hi Reza,

    In My Scenario the fiscal year is on between April to March.

    And the first fiscal week is decided based upon the first monday of the april falls in. For the current FY the first fiscal week starts on 4th April (Sunday).

    Any inputs for the same will be really helpful.

    Thanks,
    GSV

    • Hi.
      In this case, you need to find the first Monday of April, then find out what is the day before that. consider that as start of fiscal year, everything before that will be fiscal year-1. this can be done as a “Add Custom Column” experience in Power Query with a bit of expression.
      Cheers
      Reza

  • Hey, i’m facing some issues creating the right Date Dimension for my Dataset,
    So the dataset are a bunch of Reservations, I have:
    Created at Date
    Arrival Date:
    Departure Date:
    Canceled Date:

    As i understand i have 4 Date Dimensions. How would look the Setup in this case? Do i have to create a Datetable for each Dimension? If so, when i want to see the Rooms Revenue for a Arrival Time (for example Relative date the next two Calendar Month) but only the ones “Created at:” the last 2 Calendar Month, what Meassure do i create and how do i filter propperly to be abble to add the Sameperiod Lastyear Measure to it?

    Rooms Revenue by Arrival date = Calculate(Sum(‘Reservation'[Rooms Revenue]), ‘Reservation'[Arrival]))
    Rooms Revenue by Arrival SameperiodLastyear = Calculate(Sum(‘Reservation'[Rooms Revenue]), Sameperiodlastyear(‘Reservation'[Arrival]))
    ?

    Do i need to create one global Calendar Tabl and connect the Coulums with dates to date, or do i need to make 4 seperate Date tbls forr each Dimension? How will the filter to be set to work with 2 dimensions in one visual?

    thanx in advance
    Nic

    • Hi Nic
      You need to create one date dimension, and then role-playing dimensions from that one as many as needed.
      I explained how that process works here.
      Cheers
      Reza

Leave a Reply