Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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
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
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
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
26 | |
24 | |
12 | |
11 | |
10 |