Forum Discussion

jkaufman's avatar
jkaufman
Regular Visitor
8 years ago
Solved

DAX Create column LAST status based on MAX date

I'd like to add a few fields that calculate the Last Status based on the ID. That is, for the 'ID' = 54EBA584-0FDA-4766-B3B1-7FC0709C92BA the max 'Status Date' is 10/29/2018. So I would like a column...
  • v-yuta-msft's avatar
    v-yuta-msft
    8 years ago

    Hi jkaufman,

     

    To achieve your requirement, create two calculate column using DAX as below:

    Max Status Date = CALCULATE(MAX(Table1[Status Date]), ALLEXCEPT(Table1, Table1[ID]))

Result = CALCULATE(MAX(Table1[Status]), FILTER(ALLEXCEPT(Table1, Table1[ID]), Table1[Status Date] = Table1[Max Status Date]))

     

     

    Regards,

    Jimmy Tao

v-yuta-msft's avatar
v-yuta-msft
Community Support
8 years ago

Hi jkaufman,

 

To achieve your requirement, create two calculate column using DAX as below:

Max Status Date = CALCULATE(MAX(Table1[Status Date]), ALLEXCEPT(Table1, Table1[ID]))

Result = CALCULATE(MAX(Table1[Status]), FILTER(ALLEXCEPT(Table1, Table1[ID]), Table1[Status Date] = Table1[Max Status Date]))

 

 

Regards,

Jimmy Tao

Anonymous's avatar
Anonymous
Not applicable
3 years ago

I've been wracking my brain trying to figure out how to do this!! Thank you so much for your solution!

My code is pretty similar to yours, except I didn't use the Filter(AllExcept) function. I just used the following code. May I ask what does the Filter(AllExcept) does for this code, and why wouldn't it work without it?

Again, thank you so much.

CALCULATE(MAX(Table1[Status]), Table1[Status Date] = Table1[Max Status Date])