Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
cam0082
Frequent Visitor

DAX: Measure to Separate This Year from Last Year

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"))

 

 1.PNG

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

 

 

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@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"
)

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-chuncz-msft
Community Support
Community Support

@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"
)

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.