DAX: Measure to Separate This Year from Last Year

[cam0082]

Hi!

I have created a colum that finds tha MAX value from a table and the output would be "TY" as in"This Year".

Then, the MAX - 1 would be "LY" as in "Last Year".

TY_LY = IF(DB[Year]=MAX(DB[Year]);"TY";IF(VALUE(DB[Year])=VALUE(MAX(DB[Year]))-1;"LY";"2LY"))

 

Since it´s a column and not a measure, when I unfilter year 2020, it stills shows me year 2019 as "LY".

¿How can I create a measure that when I filter only year 2019 and 2018, the result would be TY and LY respectively?

Regards

[v-chuncz-msft · Accepted solution]

@cam0082 

You may refer to the measure below.

Measure =
SWITCH (
    SELECTEDVALUE ( DB[Year] )
        - MAXX (
            ALLSELECTED ( DB[Year] ),
            DB[Year]
        ),
    0, "TY",
    -1, "LY",
    -2, "2LY"
)

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490