Forum Discussion
Generic Last Year Measure
- 7 years ago
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
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
- Anonymous7 years agoNot applicable
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
- Anonymous7 years agoNot applicable
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