# DAX Formula for last corresponding value in Max Date



## brawnystaff (Oct 25, 2017)

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 (Oct 25, 2017)

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 (Oct 25, 2017)

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 (Oct 25, 2017)

Sorry, try this

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


----------



## brawnystaff (Oct 25, 2017)

Excellent. That one did work. Thanks.


----------



## mattr21 (Jan 27, 2020)

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


----------

