Getting the last year value for each ID with a OFF Status

[Anonymous]

Hi Everyone,

I'm new on using DAX on PowerBI and i'm trying to get a value that can looks like pretty easy but i couldn't find a way to get it using DAX. (I Could using SQL)

My data set looks something like this 

What i want is a new column giving me for each contractID the last year value where the status was OFF. So the output column "LastyearStatusOFF" should give me this 

In order to get this output column i used something like this

LastYearStatus = CALCULATE(MAX(Conctract[Year]), FILTER ( ALLEXCEPT(Contract, Contract[ID]), Status = OFF))

But i don't think it's possible to combine ALLEXCEPT and FILTER

Can you please help on this one ?

Thank you very much in advance for your help !

[amitchandak]

@Anonymous , As a new column

maxx(filter(Conctract, [ID] =earlier([ID]) && [Status] = "OFF"),[Year])

A new measure

maxx(filter(allselected(Conctract), [ID] =earlier([ID]) && [Status] = "OFF"),[Year])

[Ashish_Mathur]

Hi,

Do you want that as a calculated column in the Table itself or as a measure in your visual directly?

[parry2k · Accepted solution]

@Anonymous almost there

LastYearStatus = CALCULATE(MAX(Conctract[Year]), ALLEXCEPT(Contract, Contract[ID]), Status = OFF)

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡