Generic Last Year Measure

[AndyRothmann]

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

[Stachu · Accepted 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

[AndyRothmann]

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

[AndyRothmann]

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