Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 ha...
  • Stachu's avatar
    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:
    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