It’s been a while since I posted anything about my favourite topic calculation groups so I thought I would share this quite simple but useful example that came up in a project I am working on.
I had been using a user requested rolling 12 month period in a report that was achieved with a simple calculated column in the date table.
We were finalising the report and the user requested that the last page instead have a month year slicer that filtered the previous 12 months from that month. In other words a rolling time period of the 12 months prior to the month year selected in the slicer. I’m sure this is probably common but funnily enough I had never come across it.
You would be forgiven for thinking that I try and use calculation groups to solve all my problems but this is not the case. My first thought was naturally the model and whether this could be achieved with modelling techniques. I couldn’t think of an easy way so turned to google and quickly realised that modelling was not the quick fix I was after. I turned to DAX and of course it’s relatively simple to achieve if you want to rewrite all your measures and force the date filter with calculate. I didn’t want to do that and it was now obvious that I could have some fun with calculation groups!
The pattern used is very simple but I think it’s a useful application of calculation groups and it can be adapted for any rolling time period or any date filter you can design in DAX.
Firstly you need a month year number in your date table for this calculation. You can create one by multiplying the year by 12 and adding the month minus one in either Power Query or in DAX.
I added a month year slicer to my report page in Power BI and created the following calculation item in a Calculation Group
I then applied the calculation item as a page level filter and everything worked as expected including the tool tip on the page. Like I said….simple!, the calculation item grabs the month year value from the slicer on the page and then uses calculate to force the desired time period from this reference point. Adding it as a page level filter applies it to all the measures on the report page.
One caveat is that if you have charts with a date column component like a time series, then this will calculate for each date value present so it really only works correctly for visuals without a date column included. I was applying it to a map with a tooltip and it worked well but it filters values in DAX not columns in the model so cant restrict a date axis. I’m working on a workaround for this so will update this article once I have a proper solution.
You could create a rolling week slicer by referencing a week year number instead or you can do any date or time period you require as long as you can make the filter in DAX.
Now, before you go adding rolling time period slicers all over, you need to consider what measures your calculation item will act upon and whether the interactions will work (My friend Jeff Weir posted about this on Twitter recently). SQLBI has an article on the order of evaluation in calculate which is definitely worth reading a few times ( Order of Evaluation in CALCULATE Parameters — SQLBI). If you have a measure with REMOVEFILTERS( ‘Date’ ) somewhere in your report then the nested CALCULATE introduced by your rolling time period calculation item is likely not going to give you the result you want.
But, if like me, you have simple measures in your report without date filters built in then perhaps you will find this useful. Let me know if you know a better solution for this or have any ideas on other uses ✌️
UPDATE: In the end the requirements for rolling periods got more complex and I did end up implementing a modelling solution. If you are curious, I created the following table:
Which I then connected through a bridging table :
The Calculation Group is still a simple and fast solution if your requirements are simple :)