Date Dimension with Holidays in PowerQuery

[Nolock]

TLDR section

https://github.com/nolockcz/PowerQuery/tree/master/Date%20Dimension However, I personally recommend reading the article once before you use it in your project.

The long version

We all know how important it is to transform our data into a star schema and to have a fact table and dimension tables. Date dimension is one of those dimensions.

There are 4 ways how we can create a date dimension:

1. Date dimension is already in our DWH and we just need to load it into our model.
2. We create our own date dimension in PowerQuery.
3. We use DAX (https://www.sqlbi.com/tools/dax-date-template/).
4. We don’t care and just use the Time Intelligence of Power BI Desktop.

There are pros and cons of all these solutions. In my scenario, I need to have a date dimension already in PowerQuery because it is used in my ETL process. I’ve been searching on the internet for solutions and have found some:

• Generating A Date Dimension Table In Power Query by Chris Webb
• Script to Generate Date Dimension with Power Query M - With Financial Columns by Reza Rad
• Creating a Date Dimension with a Power Query Script by Matt Masson

Unfortunately, all these contain only the basics and therefore didn’t fulfill my expectations. At this moment, I decided to write my own date dimension in PowerQuery from scratch.

 

The core

A good date dimension used later in Power BI must start on the 1st of January and end on the 31st of December. Therefore, I personally prefer to use only the start and the end year as parameters.

There is a long list of features I have implemented: years, half-years, quarters, months, ISO weeks, days, and their differently formatted variations.

There is also a group of columns calculating a count of years backwards, vice versa for quarters, months, weeks, and days. What is it good for? You can filter your fact table by a range like MonthsBackwards <= 13 AND MonthsBackwards >= 1 to get data from the last 12 full months.

And finally, the holy grail - holidays! In many business use cases, it is crucial that we know if a definite date is a working day or not. And what is a working day? It is a day which is neither weekend nor an official holiday. Countries (or even states within a country) have their own holidays. How have I solved this diversity? I have not yet. I have prepared the dimension for holidays in the state of Baden-Württemberg in Germany. And it is your task to check out the list and remove or add new holidays – it is very simple, trust me.

How to modify the list of holidays? There is a function called fnGetAllHolidaysOfAYear which generates all holidays in a year. In Germany, most of them are based on the Easter Sunday and that is also my starting point. Then I generate a list CurrentYearHolidaysList which contains the definition of all holidays in one year.

Example New Year and Easter Monday:

// NewYear = always January 1st
[Date = #date(year, 1, 1), HolidayName = "Neujahr"],

// EasterMonday = 1 day after Easter Sunday
[Date = Date.AddDays(EasterSunday, 1), HolidayName = "Ostermontag"],

The last thing I do is translating all column names. Now you are back in the game again. It is up to you to change these translation pairs.

From community to community

There will never be a date dimension which fulfills everybody’s expectations. But we will do our best! Right?

I have committed 2 versions (https://github.com/nolockcz/PowerQuery/tree/master/Date%20Dimension😞

• The core: A date dimension without holidays 
• The extended version: the core plus holidays

Please check out the code from GitHub, hack, and report bugs and ideas for improvement. Every constructive feedback is very welcomed.
I hope that the community together can create a new etalon of a date dimension in PowerQuery!

[Sokon]

Thank you for that great article! I agree on all your points and would like to pack it in some additional info (at least for myself for future reference):

• Why not Auto Date/Time? It's not possible to extend it beyond Year, Quarter, Month and Day. Even worse, often it bloats the model enormously - test it!
• Why not using DAX with calculated columns for Year, Month etc.? Accoring to the SQLBI guys calculated columns aren't compressed as well as those coming from M.
• Why not using SQLBI DAX template? With data coming both from the query editor and DAX, I found it confusing when maintaining the model.
• Why date dimensions with complete years only? In order to guarantee the correct results when using time intelligence functions, every year in the dimension must be complete. (SQLBI)

[Anonymous]

Prezados,

Estou tentando utilizar o recurso preencimento para baixo, mas não está funcionando, no treinamento informaram que ele não funciona com celulas vazias, substituí o vazio pela palavra nul, porém não funcionou alguém pode me passar orientações para eu utilizar o referido recurso. Obrigado!

[Nolock]

Hi @Anonymous,

please could you write your question in English instead of Portuguese?

Thank you 🙂

[Eliasaki92]

Awesome!!!