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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.