My Ultimate Date Dimension! at least until I figure out a better one….

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

  1. Define Table
  2. Populate Dates Dimension
  3. Deal with 1 character days and months
  4. 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
  5. Set Fiscal Year
  6. Set Fiscal quarter
  7. Set Fiscal week

Read more

OMG a magic formula – real facepalm moment

OMG a magic formula – real facepalm moment

Ok, so we’ve all been there. We’ve all needed to pad numbers especially for dates, months are the worst, they kill when you have a mix of one and two digit numbers. While building my latest date dimension I had to combine year and week number to form a column, I’ll not bore you with everything but adding a “0” to pad out those one digits had to be done to make it all work. I looked and I looked and then BOOOM! I found it!

Then today I needed it again for something else, so my advice is write it down, store it keep it close to you, it’s going to be one of those things you use loads during key times then you’ll not touch it for months, days or hours?



Read more