Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi all,
Got a table with year in number format,items and amounts
2020 | item01 | 10000 €
2020 | item02 | 3244 €
2021 | item 01 | 2312 €
2021 | item 02 | 6435 €
2022 | item 01 | 4334 €
2022 | item 02 | 5634 €
A measure = sum(table[amount])
A filter on the year = 2022
Measure show : 9968 in a smart card
I'd like to show in another smart card the 2021 amount ( 8747) by calculating something like
Calculate [measure], [year]-1
Thank you
Solved! Go to Solution.
@Jay20024
Try this below formula for dynamic approach
PY =
CALCULATE(
[Sum Value],
FILTER(
ALL( Source[Year] ),
Source[Year] = MAX( Source[Year] ) -1
) )
screenshot attached
Hope it helps
Regards
sanalytics
@Jay20024
Try this below formula for dynamic approach
PY =
CALCULATE(
[Sum Value],
FILTER(
ALL( Source[Year] ),
Source[Year] = MAX( Source[Year] ) -1
) )
screenshot attached
Hope it helps
Regards
sanalytics
I need a get the result for each year to display them in a pivot table like that
So I can add them to a scorecard, make calculations. I need a behavior like previousyear
So each time a select a year in the filter, I get the line related to the selected year, total amount and total amount LY. Many thanks for your help.
@Jay20024
I suggest you to replicate above all things in power bi and check. it seems that you have not tried or you have not understood the code.
MAX is respecting the filter context which is coming from your slicer as well.
Check from your end once.
Regards
sanalytics
Indeed, There was a error. It works nice !
Hi @Jay20024 ,
You have a few options here. You could add a filter on the visual in the filter pane as well. Here you can even do relative filters.
Or you could use a measure like this:
Measure 2021 = CALCULATE([Measure], 'Table'[Year] = 2021)
Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
For the year i need to take in account the sected value of the filter. Thanks
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
34 | |
14 | |
12 | |
11 | |
10 |