Switch column chart axis by day, week, month, year with buttons

[zdenek_moravec]

I have a table with a date column and a key figure value.

I create a column chart with date on X axis and the key figure in values.

The request is to switch the X axis from date (represented by day by default) to weeks, months, years granularity with a set of buttons.

After one hour googling, I have created a calender table with columns date, week, month, year

and 4 charts, each with different column name (from the calender table) in the values field

And finally the trick with 4 charts overlayed and 4 buttons, which toggle each chart with the help of bookmarks and selection pane.

But isn't there a easier way to do this? This looks like overkill.

Thank you

[AllisonKennedy]

@zdenek_moravec 

That's amazing! I love buttons. 

My approach would be to use Drill down in 1 visual. So put Year Month in Axis, Month underneath, Day under that, etc all in the same visual in the correct order. You can probably still set up bookmarks to make this work with buttons, or just train users to use the built in drill down functionality.

UPDATED to add: Use the parallel disconnected arrows for drill down to get the same effect as your separate charts. 

The Microsoft documentation doesn't use a great example, but here's the info on drill down so you can see what built in buttons I mean: https://docs.microsoft.com/en-us/power-bi/consumer/end-user-drill

Also, I suggest making your date table in Power Query if you can, it will speed up your report: https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

[zdenek_moravec]

Hello Allison

The idea with hierarchy is good, I remember, I tried it, but the disadvantage is, that the hierarchy function groups the same month number, week numbers from all years. So I see month 10 in one bar as sum of months 10.2019 and 10.2020. Therefore I created year-month column additionally to month column to see month 10 in two columns - 10.2019 and 10.2020.