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

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 275 members 3,001 guests
Please welcome our newest community members: