DAX Formula for last corresponding value in Max Date

[brawnystaff]

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.

 

[Matt Allington]

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

 

[brawnystaff]

Hmm. Variable "Last Date" worked and displays the last date, but last status comes back blank. When measure was created, no errors were displayed.

I uploaded a sample file at the following URL:

https://drive.google.com/open?id=0BweyQNyritHaYVdsWVI2UV9uUTQ

Any ideas as to what the problem is?

 

[Matt Allington]

Sorry, try this

=
CALCULATE (
IF ( HASONEVALUE ( Table1[Status Code] ), VALUES ( Table1[status code] ) ),
FILTER ( ALLEXCEPT ( Table1, Table1[Name] ), Table1[date] = Max(Table1[Date] ))
)

 

[brawnystaff]

Excellent. That one did work. Thanks.

 

[mattr21]

Any idea how to adjust this formula so it works for text values and not just numerical values?