Forum Discussion
Generic Last Year Measure
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
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:
MeasureValueDateSales 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
3 Replies
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:
MeasureValueDateSales 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