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. 

I received help in another thread to create measures for the first two cards. These measures are below:

Base Year New =

CALCULATE(

COUNTA('Tag Unpivot'[Request ID]),

FILTER(

ALLSELECTED('Tag Unpivot'),

'Tag Unpivot'[Request Date].[Year] IN VALUES('Tag Unpivot'[Request Date 2].[Year])

),

FILTER(

ALLSELECTED('Tag Unpivot'),

'Tag Unpivot'[Tag (groups)] IN VALUES('Tag Unpivot'[Tag (groups)])

),

FILTER(ALLSELECTED('Tag Unpivot'),

'Tag Unpivot'[Tag] IN VALUES('Tag Unpivot'[Tag]

)))

Comparison Year Request Count New =

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)])

),

FILTER(ALLSELECTED('Tag Unpivot'),

'Tag Unpivot'[Tag] IN VALUES('Tag Unpivot'[Tag]

)))

These seem to be working fine, but the YoY calculation is returning blank or zero depending on whether the slicers are acting on it or not. 

YOY Percentage Change New 2 =

DIVIDE([Base Year New]-[Comparison Year Request Count New],[Base Year New])

I would appreciate any help anyone can offer! Thank you!

[rajendraongole1 · Accepted solution]

Hi @acrinard  -Your YOY Percentage Change New 2 measure might be returning blank or zero as denominator is vlaue returns blank. 

Base Year New =
VAR SelectedBaseYear = SELECTEDVALUE('Tag Unpivot'[Request Date 2].[Year])
RETURN
CALCULATE(
COUNTA('Tag Unpivot'[Request ID]),
'Tag Unpivot'[Request Date].[Year] = SelectedBaseYear
)

Measure for updated Comparison :

Comparison Year Request Count New =
VAR SelectedComparisonYear = SELECTEDVALUE('Tag Unpivot'[Request Date].[Year])
RETURN
CALCULATE(
COUNTA('Tag Unpivot'[Request ID]),
'Tag Unpivot'[Request Date].[Year] = SelectedComparisonYear
)

final calculation should be:YOY Percentage Change measure

YOY Percentage Change New 2 =
VAR BaseYearCount = [Base Year New]
VAR ComparisonYearCount = [Comparison Year Request Count New]
RETURN
IF(
BaseYearCount > 0,
DIVIDE(ComparisonYearCount - BaseYearCount, BaseYearCount)
)

Hope this helps.

[v-pnaroju-msft]

Hi acrinard,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thank you.

[v-pnaroju-msft]

Hi acrinard,

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

If you found our response useful, kindly mark it as the accepted solution and provide kudos to guide other members.

Thank you.

[v-pnaroju-msft]

Hi acrinard,

Thankyou for the update.
Please find below the DAX measures that may help in resolving the issue:

Base Year New =

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

RETURN

CALCULATE(

COUNTA('Tag Unpivot'[Request ID]),

'Tag Unpivot'[Request Date].[Year] = SelectedBaseYear,

KEEPFILTERS(ALLSELECTED('Tag Unpivot'[Tag])),

KEEPFILTERS(ALLSELECTED('Tag Unpivot'[Tag (groups)]))

)

Comparison Year Request Count New =
VAR SelectedComparisonYear = SELECTEDVALUE('Tag Unpivot'[Request Date].[Year])
RETURN
CALCULATE(
COUNTA('Tag Unpivot'[Request ID]),
'Tag Unpivot'[Request Date].[Year] = SelectedComparisonYear,
KEEPFILTERS(ALLSELECTED('Tag Unpivot'[Tag])),
KEEPFILTERS(ALLSELECTED('Tag Unpivot'[Tag (groups)]))
)

YOY Percentage Change New =
VAR BaseYearCount = [Base Year New]
VAR ComparisonYearCount = [Comparison Year Request Count New]
RETURN
IF(
BaseYearCount > 0 && NOT ISBLANK(BaseYearCount),
FORMAT(
DIVIDE(ComparisonYearCount - BaseYearCount, BaseYearCount, 0),
"0.00%"
),
"N/A"
)

If you find our response helpful, we kindly request you to mark it as the accepted solution and provide kudos. This will assist other community members who may have similar queries.

Thank you.

[v-pnaroju-msft]

Hi acrinard,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank you.

[acrinard]

Hi @v-pnaroju-msft , thank you for your response. Unfortunately these measures have the same issue - if all slicers are acting on base year and comparison year they work, if all slicers are acting on the YoY calculation it returns zero, and if none are acting on it it returns blank. I'm not sure what's going wrong, but thank you for your help.

[v-pnaroju-msft · Accepted solution]

Thank you, @rajendraongole1 , for your response.

Hi @acrinard,

Please find below the DAX measures that may help in resolving the issue:

Base Year New =

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

RETURN

CALCULATE(

COUNTA('Tag Unpivot'[Request ID]),

'Tag Unpivot'[Request Date].[Year] = SelectedBaseYear,

ALLSELECTED('Tag Unpivot'[Tag]),

ALLSELECTED('Tag Unpivot'[Tag (groups)])

)

Comparison Year Request Count New =

VAR SelectedComparisonYear = SELECTEDVALUE('Tag Unpivot'[Request Date].[Year])

RETURN

CALCULATE(

COUNTA('Tag Unpivot'[Request ID]),

'Tag Unpivot'[Request Date].[Year] = SelectedComparisonYear,

ALLSELECTED('Tag Unpivot'[Tag]),

ALLSELECTED('Tag Unpivot'[Tag (groups)])

)

YOY Percentage Change New =

VAR BaseYearCount = [Base Year New]

VAR ComparisonYearCount = [Comparison Year Request Count New]

RETURN

IF(

AND(BaseYearCount > 0, NOT ISBLANK(BaseYearCount)),

DIVIDE(ComparisonYearCount - BaseYearCount, BaseYearCount, 0),

BLANK()

)

If you find our response helpful, we kindly request you to mark it as the accepted solution and provide kudos. This will assist other community members who may have similar queries.

Thank you.

[acrinard]

Hi @rajendraongole1 , thank you for your response! I made three new measures based on your code, and the first two again are working, but the third is still either returning blank or zero. For example, when Base Year returns 18 and Comparison Year returns 77, the YoY returns blank if all slicers are acting on it and zero if the base year and comparison year slicers are not acting on it.

Could it be an issue of which slicers are acting on which visuals?

[rajendraongole1 · Accepted solution]

Hi @acrinard  -Your YOY Percentage Change New 2 measure might be returning blank or zero as denominator is vlaue returns blank. 

Base Year New =
VAR SelectedBaseYear = SELECTEDVALUE('Tag Unpivot'[Request Date 2].[Year])
RETURN
CALCULATE(
COUNTA('Tag Unpivot'[Request ID]),
'Tag Unpivot'[Request Date].[Year] = SelectedBaseYear
)

Measure for updated Comparison :

Comparison Year Request Count New =
VAR SelectedComparisonYear = SELECTEDVALUE('Tag Unpivot'[Request Date].[Year])
RETURN
CALCULATE(
COUNTA('Tag Unpivot'[Request ID]),
'Tag Unpivot'[Request Date].[Year] = SelectedComparisonYear
)

final calculation should be:YOY Percentage Change measure

YOY Percentage Change New 2 =
VAR BaseYearCount = [Base Year New]
VAR ComparisonYearCount = [Comparison Year Request Count New]
RETURN
IF(
BaseYearCount > 0,
DIVIDE(ComparisonYearCount - BaseYearCount, BaseYearCount)
)

Hope this helps.