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
Icon for Community Support rankCommunity 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
6 years ago

 

I faced a similar problem, but the solution failed with the external filter. This Doesn’t work when we apply an external or explicit filter on a Status date.