Forum Discussion
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-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 |
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
6 Replies
v-yuta-msftCommunity Support
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
Thanks for your solution! Please help count people by the last status.
- Ashish_Mathur
Super User
Hi,
Share some data, explain the question and show the expected result.
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])
