Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

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

 

 

3 REPLIES 3
Anonymous
Not applicable

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?

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!

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

TheoC
Super User
Super User

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.

 

TheoC_0-1658797319523.png

 

Hope this helps!

Theo

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.