PowerBi – Date Hierarchy

So I’ve been using PowerBi quite a bit lately to build visualization tools, and I’ve noticed while the Drill Down Feature is very helpful, the date hierarchies by default do not always work the way I think they should.

A Quick Example

Basic Column Chart with two Years Displayed

Drilling Into a Year

As you can see, we get the 4 columns of 2016, with a very small Q1, rising through until Q4 is by far the largest value.

This works exactly as I would expect it to, however when I expand down a level my results don’t show as I would expect.

As you can see when I just expand down a level, the data displays 4 quarters again. This time, however, the Q1 number outpaces the Q4 number by a fairly substantial margin. Why is this?

Well the native grouping sees the values of Q1 across multiple years as being part of the same category. Yikes! That’s probably not what you really want to show. You probably need to show Q1 – 2016, Q2 – 2016 etc.

So how to do that?

First I took my Date Table (If you don’t have one, here’s a good starting post on Date Tables) and created a second table using the “Reference” ability in PowerBI. From there I grouped everything down by Year & Quarter.

Next I added a column that included a string representation of the Quarter and Year.

Once I have my quarter and year table completed, I returned to my visual, where instead of using the normal date hierarchy I added the Quarterly drill down. I proceeded to do the same thing with the Month and Year until the Drill downs performed the way they were expected to behave.

Here’s the final chart, with the drill through expanded to the Quarters to show what the result looked like.

Hope this helps someone with their Date Hierarchy grouping issues. If someone else solved this another way, I’d love to hear about it!

Mike