Blog Post

Power BI Community Blog
3 MIN READ

Date Dimension with Holidays in PowerQuery

Nolock's avatar
Nolock
Icon for Resident Rockstar rankResident Rockstar
7 years ago
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 ...
Updated 7 years ago
Version 8.0
how to
tips & tricks
Nolock's avatar
Icon for Resident Rockstar rankResident Rockstar
Joined February 24, 2019
View Profile
Power BI Community Blog
On this blog you will find articles, guides and information created by our community members and, from time to time, roundups and community news. If you are interested in becoming a community blog author, send a private message to Natalie_iTalent. An overview of the community blogs can be found here.
Sokon's avatar
Sokon
Icon for Advocate V rankAdvocate V
7 years ago

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)