Hardcoding a calculation group in a measure

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.

2 Replies to “Hardcoding a calculation group in a measure

  1. Hi Kasper,

    Unfortunately we deal with the same limitiations as you mentioned in your post.
    We are using Calculation Groups with the intention of not having to create additional measures for every time calculation we want to use.
    In your example you create additional measures, which I see as a workaround and would force us to add hundreds of measures. 🙁

    As you mentioned, this is caused by limitations of some Visuals. Can you share with us if these Visuals are being modified to work correctly ? Any timeframe when we can expect this?

    Thanks,
    Frans

  2. Hi Kasper,
    you might be able to help.

    I am doing exactly what you show in your article. My only problem is that my measure looses the “format”.
    If I apply a calculation group as a filter to a visual for example it works perfectly. If I filter a measure in a DAX formula it looses the format.

    Did you not this behaviour?
    Best,P

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.