How to Subtract CY sales from PY sales based on CY being chosen from SLICER visual

[Anonymous]

Hello! I'm trying to find a way to get the difference between the CY and PY sales, however I would like the user to be able to choose the year that would equate to CY in the slicer dropdown list so the formula needs to be dynamic and not static.

For instance if we have 3 years with sales totals below, when the user selects 2022 in the slicer visual I would want to see an output of 5,000 (2022-2021). If they then select 2023, it should update to 7,000.  Please help!!

2023: 17,000

2022: 10,000

2021: 5,000

[Anonymous]

Hi @TheoC thanks for your quick reply! I forgot to mention that the years in the slicer visual will be pulling from a separate table (just containing years) that is mapped tothe sales table. Does that change the measure at all?

[TheoC]

Hi @Anonymous 

It shouldn't impact it at all because of the relationship but if you find it's not giving you what you want, just change the factTable[Year] reference to YearTable[Year].

Hope this helps!

[TheoC]

Hi @Anonymous 

You can use a measure like the following to get what you're after:

Var $ YoY = 

VAR _1 = CALCULATE ( SUM ( Sales[Sales] ) , FILTER ( ALL ( Sales ) , Sales[Year] = MAX ( Sales[Year] ) -1 ) )
VAR _2 = SUM ( Sales[Sales] )

RETURN

_2 - _1

The below is the output based on the data I created.

Hope this helps!

Theo