Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Together,
i often have the requirement to show the previous year value of a measure. Currently i create a additional "Sales Amount PY" measure for every measure, which is in the model. So i have for example 20 instead of 10 measures.
So is there an chance to create a dynamic measure which automatically recognizes the "original Measure", so that only need to add a single previous year measure? Or is there a workaround?
Thanks in advance
Andy
Solved! Go to Solution.
that depends on your data structure, and how you specify PY
if you have a slicer that selects CY/PY then you could do something like this:
Smart Sales v1 = SWITCH ( SELECTEDVALUE ( Slicer[CY_PY] ), "CY", SUM ( Sales[Value] ), "PY", CALCULATE ( SUM ( Sales[Value] ), SAMEPERIODLASTYEAR ( Calendar[Date] ) ), BLANK () )
if you have a data structure like this:
MeasureValueDate
Sales | 1 | Tuesday, 1 January 2019 |
Quantity | 2 | Tuesday, 1 January 2019 |
Profit | 3 | Tuesday, 1 January 2019 |
Cost | 4 | Tuesday, 1 January 2019 |
Sales | 5 | Tuesday, 1 January 2019 |
Quantity | 6 | Tuesday, 1 January 2019 |
Sales | 6 | Monday, 1 January 2018 |
Quantity | 5 | Monday, 1 January 2018 |
Profit | 4 | Monday, 1 January 2018 |
Cost | 3 | Monday, 1 January 2018 |
Sales | 2 | Monday, 1 January 2018 |
Quantity | 1 | Monday, 1 January 2018 |
you could do something like this
SmartValue = SWITCH ( SELECTEDVALUE ( Slicer[CY_PY] ), "CY", SUM ( Table[Value] ), "PY", CALCULATE ( SUM ( Table[Value] ), SAMEPERIODLASTYEAR ( Calendar[Date] ) ), BLANK () )
and then sales would be just:
Sales = CALCULATE([SmartValue], Table[Measure] = Sales)
this solution has the benefit that you can also add YTD, QTD, MTH calculation within same logic
that depends on your data structure, and how you specify PY
if you have a slicer that selects CY/PY then you could do something like this:
Smart Sales v1 = SWITCH ( SELECTEDVALUE ( Slicer[CY_PY] ), "CY", SUM ( Sales[Value] ), "PY", CALCULATE ( SUM ( Sales[Value] ), SAMEPERIODLASTYEAR ( Calendar[Date] ) ), BLANK () )
if you have a data structure like this:
MeasureValueDate
Sales | 1 | Tuesday, 1 January 2019 |
Quantity | 2 | Tuesday, 1 January 2019 |
Profit | 3 | Tuesday, 1 January 2019 |
Cost | 4 | Tuesday, 1 January 2019 |
Sales | 5 | Tuesday, 1 January 2019 |
Quantity | 6 | Tuesday, 1 January 2019 |
Sales | 6 | Monday, 1 January 2018 |
Quantity | 5 | Monday, 1 January 2018 |
Profit | 4 | Monday, 1 January 2018 |
Cost | 3 | Monday, 1 January 2018 |
Sales | 2 | Monday, 1 January 2018 |
Quantity | 1 | Monday, 1 January 2018 |
you could do something like this
SmartValue = SWITCH ( SELECTEDVALUE ( Slicer[CY_PY] ), "CY", SUM ( Table[Value] ), "PY", CALCULATE ( SUM ( Table[Value] ), SAMEPERIODLASTYEAR ( Calendar[Date] ) ), BLANK () )
and then sales would be just:
Sales = CALCULATE([SmartValue], Table[Measure] = Sales)
this solution has the benefit that you can also add YTD, QTD, MTH calculation within same logic
Hi,
first i want to thank you very much for the help.
I will try it tomorrow and take a look if it works for me.
Greetings andy
Hi,
first i want to thank you very much for the help.
I will try it tomorrow and take a look if it works for me.
Greetings andy
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |