cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jkaufman Visitor
Visitor

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 populated with 10/29/2018 for all records with 'ID' = 54EBA584-0FDA-4766-B3B1-7FC0709C92BA.

 

Furthermore, and more importantly, I'd like a column that returns the latest 'Status Outcome' correlated to the MAX 'Status Date'. So for the same example a column with 'Last_Status' populated with ACTIVE for all records with 'ID' = 54EBA584-0FDA-4766-B3B1-7FC0709C92BA.

 

Is this possible to do in DAX?

 

ID       Status Date       Status      Status Outcome

54EBA584-0FDA-4766-B3B1-7FC0709C92BA8/15/2018PendingP01
54EBA584-0FDA-4766-B3B1-7FC0709C92BA8/20/2018PendingP02
54EBA584-0FDA-4766-B3B1-7FC0709C92BA8/28/2018PendingP03
54EBA584-0FDA-4766-B3B1-7FC0709C92BA9/3/2018ActiveA01
54EBA584-0FDA-4766-B3B1-7FC0709C92BA10/1/2018ActiveA01
54EBA584-0FDA-4766-B3B1-7FC0709C92BA10/29/2018ActiveA01
162E0BD7-E850-4F9C-BA38-3540348A87788/22/2018PendingP01
162E0BD7-E850-4F9C-BA38-3540348A87788/27/2018PendingP02
162E0BD7-E850-4F9C-BA38-3540348A87789/4/2018PendingP03
162E0BD7-E850-4F9C-BA38-3540348A87789/5/2018CancelledC05
8B876A64-6F52-48A5-8FF3-EBF3040DF15E8/24/2018PendingP01
8B876A64-6F52-48A5-8FF3-EBF3040DF15E8/29/2018PendingP02
8B876A64-6F52-48A5-8FF3-EBF3040DF15E9/8/2018PendingP03
8B876A64-6F52-48A5-8FF3-EBF3040DF15E9/16/2018ActiveA01
8B876A64-6F52-48A5-8FF3-EBF3040DF15E10/14/2018ActiveA01
8B8716564-6F52-48A5-8FF3-EBF3040GD238/30/2018PendingP01
3156fsd64-6F52-48A5-8FF3-EBF3040GD2568/31/2018PendingP01
3156fsd64-6F52-48A5-8FF3-EBF3040GD2579/3/2018PendingP01
3156fsd64-6F52-48A5-8FF3-EBF3040GD2579/6/2018PendingP02
3156fsd64-6F52-48A5-8FF3-EBF3040GD2589/5/2018PendingP01
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: DAX Create column LAST status based on MAX date

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

 捕获.PNG

 

Regards,

Jimmy Tao

1 REPLY 1
Highlighted
Community Support Team
Community Support Team

Re: DAX Create column LAST status based on MAX date

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

 捕获.PNG

 

Regards,

Jimmy Tao