As you progress in Power BI (and Power Pivot), you quickly find you need a date dimension, the problem is that date dimensions are difficult to build and even more difficult if you want them to be more than just a list of dates.
“Stop I’m confused… how can a list of dates be more?”
The role of a good date dimension is indeed to be more than just a list of dates, there are functions in Power BI to produce a list of dates, the issue is that you want something that is more. I’ll be honest at this point before we dive into the queries, I use, I cannot remember where I got the original source, I found three or four different date dimension SQL queries initially and then started pulling them together and adjusting them to fit my needs. For me we were going to have the majority of our data coming from a SQL server so it makes sense to have a SQL date dimension, based on what we did and after doing some benchmarking within Power BI I would recommend housing it on a Database, putting it in Power BI or excel won’t stop things working, but I have got best results from this method.
The important thing about the first bit is that there are many, many scripts out there to do what you need. Typically they have the same format.
I’ll make these source files available to download
Populate Dates Dimension
- Deal with 1 character days and months
- Set Fiscal Year Months – where I work we use August to July so I needed to add a case statement here, case statements sound more complex than they are
- Set Fiscal Year
- Set Fiscal quarter
- Set Fiscal week