cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AndyRothmann Frequent Visitor
Frequent Visitor

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Generic Last Year Measure

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

3 REPLIES 3
Super User
Super User

Re: Generic Last Year Measure

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 Frequent Visitor
Frequent Visitor

Re: Generic Last Year Measure

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

Highlighted
AndyRothmann Frequent Visitor
Frequent Visitor

Re: Generic Last Year Measure

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