How to find and return same value based on latest date found in another column?

[Anonymous]

Hi all,

I am needing a calculated column that returns the most recent value that appears 'Ordinary/Non-Ordinary' column and replicate that value for all rows for the same Provider ID (regardless of Week Ending).

In the example below, as 'Ordinary' was the value that appeared in the 'Ordinary/Non-Ordinary' column on the 22nd of October (deemed to be the latest date that Provider ID 10025304 appeared in the dataset), therefore I want to see 'Ordinary' returned in ALL rows in the 'Ordinary/Non-Ordinary (latest)' column. However it is returning an error:

Ordinary/Non-Ordinary? (latest) = lookupvalue('Provider ID'[Ordinary/Non-Ordinary?],
'Provider ID'[Week Ending],'Provider ID'[Latest Week Provider ID exists],
'Provider ID'[Provider ID],'Provider ID'[Provider ID])

For some reason I was able to achieve what I was hoping for in the 'Provider (latest) column. See how it returns 'PM EN1' for ALL rows for Provider ID 10025304 (regardless of week ending date). This Provider ID used to have 'PM RN1' prior to the 1st of October, and the dax function in 'Provider (latest)' ignores that as it was before the latest date of the 22nd of October.

Provider (latest) = lookupvalue('Provider ID'[Provider],
'Provider ID'[Provider ID],'Provider ID'[Provider ID],
'Provider ID'[Week Ending],filter(ALL('Provider ID'[Week Ending]),'Provider ID'[Week Ending]='Provider ID'[Latest Week Provider ID exists]))

I'm not sure how to resolve the error I'm experiencing in the 'Ordinary/Non-ordinary? (latest)' column though.

Any help would be much appreciated!

[ERD · Accepted solution]

Hi @Anonymous , try this pattern:

Column =
VAR _dt =
    CALCULATE (
        MAX ( 'Provider ID'[Week Ending] ),
        ALLEXCEPT ( 'Provider ID', 'Provider ID'[Provider ID] )
    )
VAR _status =
    CALCULATE (
        MAX ( 'Provider ID'[Ordinary/Non-Ordinary?] ),
        ALLEXCEPT ( 'Provider ID', 'Provider ID'[Provider ID] ),
        'Provider ID'[Week Ending] = _dt
    )
RETURN
    _status

[Anonymous]

Brilliant, thanks @ERD. Worked as required!