DAX statement to show only most recent text value

[Conance-steven]

I have a simple table with progress statusses, per project, linked to a calendar date. I'm looking for a DAX statement which shows only the most recent (latest available date) status per project. That should be the statusses for February; Amber, Green, Amber.

My current DAX statement is:

Latest status  with hasonevalue = IF (
    HASONEVALUE ( 'Status'[Project] ),
    LASTNONBLANK ( 'Status'[Status], LASTDATE ( 'Status'[Date] ) ),
    BLANK ()
)

The output for this statement is as shown below and not what I was expecting. 

Can someone explain to me why this is the output, and how I can adjust the DAX statement to fix this?

I also tried with FIRSTNONBLANK, different output but also not as expected.

[Conance-steven]

Many thanks to both. I'm going for the shorter code solution 🙂 

[AntrikshSharma]

@Conance-steven  Here is another way to calculate status:

Latest Status = 
VAR CurrentVisibleProject =
    SELECTEDVALUE ( Steven[Project] )
VAR MostRecentDate =
    CALCULATE (
        MAX ( Steven[Date] ),
        TREATAS ( { CurrentVisibleProject }, Steven[Project] )
    )
VAR Result =
    CALCULATE (
        CONCATENATEX ( VALUES ( Steven[Status] ), Steven[Status], ", " ),
        TREATAS ( { CurrentVisibleProject }, Steven[Project] ),
        TREATAS ( { MostRecentDate }, Steven[Date] )
    )
RETURN
    Result

[harshnathani · Accepted solution]

Hi @Conance-steven ,

Please see if this is the solution you are looking for.

Attached is the sheet.

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)