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!

[123abc]

Here's a revised DAX formula for your 'Ordinary/Non-ordinary? (latest)' column:

Ordinary/Non-ordinary? (latest) =
VAR MaxDate = MAX('Provider ID'[Week Ending])
RETURN
CALCULATE(
VALUES('Provider ID'[Ordinary/Non-Ordinary?]),
FILTER('Provider ID', 'Provider ID'[Week Ending] = MaxDate)
)

In this formula:

1. We first use a variable (MaxDate) to calculate the maximum date in the 'Week Ending' column for each Provider ID.

2. Then, we use the CALCULATE function to filter the 'Provider ID' table to include only rows where the 'Week Ending' matches the maximum date (MaxDate).

3. Finally, we use VALUES to retrieve the distinct value of 'Ordinary/Non-Ordinary?' for the filtered rows. This gives you the most recent 'Ordinary/Non-Ordinary?' value for each Provider ID.

This formula should return the correct value in the 'Ordinary/Non-ordinary? (latest)' column for each row, based on the most recent date for each Provider ID.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Anonymous]

Thanks for the reply.

I have update the function with your advised solution, however a circular dependency error has arisen. And the column is not showing 'Ordinary' in all rows as it is expected to: