DAX Create column LAST status based on MAX date

[jkaufman]

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-7FC0709C92BA	8/15/2018	Pending	P01
54EBA584-0FDA-4766-B3B1-7FC0709C92BA	8/20/2018	Pending	P02
54EBA584-0FDA-4766-B3B1-7FC0709C92BA	8/28/2018	Pending	P03
54EBA584-0FDA-4766-B3B1-7FC0709C92BA	9/3/2018	Active	A01
54EBA584-0FDA-4766-B3B1-7FC0709C92BA	10/1/2018	Active	A01
54EBA584-0FDA-4766-B3B1-7FC0709C92BA	10/29/2018	Active	A01
162E0BD7-E850-4F9C-BA38-3540348A8778	8/22/2018	Pending	P01
162E0BD7-E850-4F9C-BA38-3540348A8778	8/27/2018	Pending	P02
162E0BD7-E850-4F9C-BA38-3540348A8778	9/4/2018	Pending	P03
162E0BD7-E850-4F9C-BA38-3540348A8778	9/5/2018	Cancelled	C05
8B876A64-6F52-48A5-8FF3-EBF3040DF15E	8/24/2018	Pending	P01
8B876A64-6F52-48A5-8FF3-EBF3040DF15E	8/29/2018	Pending	P02
8B876A64-6F52-48A5-8FF3-EBF3040DF15E	9/8/2018	Pending	P03
8B876A64-6F52-48A5-8FF3-EBF3040DF15E	9/16/2018	Active	A01
8B876A64-6F52-48A5-8FF3-EBF3040DF15E	10/14/2018	Active	A01
8B8716564-6F52-48A5-8FF3-EBF3040GD23	8/30/2018	Pending	P01
3156fsd64-6F52-48A5-8FF3-EBF3040GD256	8/31/2018	Pending	P01
3156fsd64-6F52-48A5-8FF3-EBF3040GD257	9/3/2018	Pending	P01
3156fsd64-6F52-48A5-8FF3-EBF3040GD257	9/6/2018	Pending	P02
3156fsd64-6F52-48A5-8FF3-EBF3040GD258	9/5/2018	Pending	P01

[technolog]

@jkaufman @sophy 
https://community.powerbi.com/t5/Quick-Measures-Gallery/Power-BI-Get-the-Last-Latest-Value-of-a-Cate...

[v-yuta-msft · Accepted solution]

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]

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

[sophy]

Thanks for your solution! Please help count people by the last status.  

[Ashish_Mathur]

Hi,

Share some data, explain the question and show the expected result.

[Anonymous]

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.