DAX Formula for last corresponding value in Max Date

brawnystaff

Board Regular
Joined
Aug 9, 2012
Messages
104
Office Version
  1. 365
I am using Excel 2016 and have the following three columns in a worksheet:

Column A: Name
Column B: Date
Column C: Status Code

There are duplicate names that appear in Column A, what changes is the Date and possibly the Status Code.

When putting the sheet above into a Pivot table and adding to the Data Model, trying to get the following results:

Rows: Name
Values: Max Date and corresponding Status Code that appears on the same row with the Max date

I am able to get the Max Date by right-clicking on the Range Name in the Pivot Table and Adding a Measure and putting in the following DAX formula: Max([Date]). However, I am not able to figure out how to get the corresponding Status Code. Any ideas?

Note that I am able to figure out the above by not using a Pivot Table and instead using some array formulas (Index/Max/Row) to get the answer I need, I just want to know if it is possible using the Pivot Table/DAX formula approach.. Thanks.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Try this (not tested)
Last date=max(table[date])
last status = calculate(if(hasonevalue(table[Status code]),values(table[status code])),filter(allexcept(table,table[Name]),table[date]=[Last Date]))
 
Upvote 0
Sorry, try this

=
CALCULATE (
IF ( HASONEVALUE ( Table1[Status Code] ), VALUES ( Table1[status code] ) ),
FILTER ( ALLEXCEPT ( Table1, Table1[Name] ), Table1[date] = Max(Table1[Date] ))
)
 
Upvote 0
Any idea how to adjust this formula so it works for text values and not just numerical values?
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top