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:
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.
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.
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!