AS we have seen calculation groups are great :). It offers amazing flexibility and is extremely easy to maintain. But sometimes it doesn’t do what you want due the limitations of the visuals. Let’s say I want to have a visual that shows me the sales of current year and sales Previous year on different axis (let’s say as line).
You would create something like the visual below where you want to use the same measure but apply different calc groups for each measure. But unfortunately, below visual is not as we want it to be.
As you can see, we can only apply the time calc filter once for the whole visual, but we need to apply it for each value. This is not possible through the UI. With some simple DAX it is possible though.
To create a measure that automatically applies a calculation group we use our trusted CALCULATE. To get the current value we do this:Sales Current Year =
CALCULATE ( [Sum of Sales], ‘Time Intelligence'[Time Calculation] = “Current” )
Of course, this the current period so I could have just used the measure directly, but you get the point. For Previous year we can do this:Sales Previous Year =
CALCULATE ( [Sum of Sales], ‘Time Intelligence'[Time Calculation] = “PY” )
This now gives us two measures that we can use in our visual:
Voila now we can re-use our calculation group calculations and set the filter where we need it.