Forum Discussion

acrinard's avatar
acrinard
Frequent Visitor
1 year ago

Year over year calculation with multiple slicers

I have a table "Tag Unpivot" with the following relevant columns:   Request Date Request Date 2 (a copy of the Date column but set up so that it loads new values as the data source is updated) R...
Need Help
johnbasha33's avatar
johnbasha33
Icon for Super User rankSuper User
1 year ago

Yes, the issue comes from using SELECTEDVALUE, which only works when a single value is selected. If multiple values are selected, it returns BLANK(), which causes your measure to fail.

Updated Measures

Base Year Request Count (Handles Multiple Selections)
Base Year Request Count =
CALCULATE(
COUNTA('Tag Unpivot'[Request ID]),
FILTER(
ALLSELECTED('Tag Unpivot'),
'Tag Unpivot'[Request Date 2].[Year] IN VALUES('Tag Unpivot'[Request Date 2].[Year])
),
FILTER(
ALLSELECTED('Tag Unpivot'),
'Tag Unpivot'[Tag (groups)] IN VALUES('Tag Unpivot'[Tag (groups)])
)
)

Comparison Year Request Count (Handles Multiple Selections)
Comparison Year Request Count =
CALCULATE(
COUNTA('Tag Unpivot'[Request ID]),
FILTER(
ALLSELECTED('Tag Unpivot'),
'Tag Unpivot'[Request Date].[Year] IN VALUES('Tag Unpivot'[Request Date].[Year])
),
FILTER(
ALLSELECTED('Tag Unpivot'),
'Tag Unpivot'[Tag (groups)] IN VALUES('Tag Unpivot'[Tag (groups)])
)
)
Year-over-Year Percentage Change

YOY Percentage Change =
VAR BaseCount = [Base Year Request Count]
VAR CompCount = [Comparison Year Request Count]

RETURN
IF(
BaseCount = 0,
BLANK(), -- Avoid division by zero
DIVIDE(CompCount - BaseCount, BaseCount, BLANK())
)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!



acrinard's avatar
acrinard
Frequent Visitor
1 year ago

Hi johnbasha33 , thank you for your response. 

 

The new measures for base year and comparison year may not be working correctly, as the cards only show accurate values when I edit slicer interactions so that the topic and date slicers affect them. Otherwise, they're showing static values. My understanding may be incorrect, but shouldn't they work without the slicers acting on them, and if they need the slicers, then is the correct value really being returned by the measure?

 

The YoY calculation is returning zero no matter what since the base and comparison values aren't working. 

 

I've attached some screenshots to show you what all my measures and visuals look like. Thank you so much for your help! 

 

 

 

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi acrinard,

    Thanks for reaching out to the Microsoft fabric community forum.

     

    Just following up to your previous messages, I'd like to confirm if you've successfully resolved this issue or if you need further help.

    If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
    If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

     

    I would also take a moment to thank johnbasha33, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

     

    If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

    Best Regards,
    Hammad.
    Community Support Team

     

    If this post helps then please mark it as a solution, so that other members find it more quickly.

    Thank you.