Year over year calculation with multiple slicers

[acrinard]

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)
• Request ID
• Tag

Where I have grouped the "Tag" column to create a grouping I'll call "Topics".

I want to be able to present 3 cards: one with the request count of the baseline year, one with the request count of the comparison year, and one with the year over year percentage change. I want slicers for topic, tag, baseline year, and request year. The first two cards are easy enough using slicers and using one date column for baseline and the other for comparison year. The difficulty comes with the fact that I need to create measures for these values in order to calculate year over year change. 

My baseline year slicer uses "Request Date 2" and my comparison year slicer uses "Request Date".

My issue seems to be that using SELECTEDVALUE does not allow for multiple selections. For the base year count measure, I have:

Base Year Request Count = CALCULATE(COUNTA('Tag Unpivot'[Request ID]),

'Tag Unpivot'[Request Date 2].[Year] = SELECTEDVALUE('Tag Unpivot'[Request Date 2].[Year]),

'Tag Unpivot'[Tag (groups)] = SELECTEDVALUE('Tag Unpivot'[Tag (groups)]))

Similarly for the comparison year count I have: 

Comparison Year Request Count = CALCULATE(COUNTA('Tag Unpivot'[Request ID]),

'Tag Unpivot'[Request Date].[YEAR] = SELECTEDVALUE('Tag Unpivot'[Request Date].[Year]),

'Tag Unpivot'[Tag (groups)] = SELECTEDVALUE('Tag Unpivot'[Tag (groups)]))

I previously also had a filter for selected tag but found that that broke the formula and returned blanks every time.

These measures will return values only when a single Topic is selected. I want to be able to have multiple or all topics selected and add in Tag as a filter as well. Am I going about this the wrong way?

It may be important to note that this is a dynamic dataset and I will be publishing to PowerBI service to share with colleagues.

[acrinard]

Hi @v-mdharahman , I am still having some problems and started a new thread to get more help here. I very much appreciate @johnbasha33's help! But it didn't fully solve my issue, so I haven't marked it as solved.

[v-mdharahman]

Hi @acrinard,

Can you please confirm whether the issue is still in the original question or you were able to move forward and got struck somewhere after that.

Best Regards,
Hammad.
Community Support Team

[v-mdharahman]

Hi @acrinard,

As we haven’t heard back from you, so just following up to our previous message. 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.
Thank you for your patience and look forward to hearing from you.

[v-mdharahman]

Hi @acrinard,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution so that other community members can find it easily.

Thank you.

[v-mdharahman]

Hi @acrinard,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[johnbasha33]

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]

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! 

[v-mdharahman]

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.