Fetch value from another table where date is more recent

[nok]

Hi!

I have two tables that follow this structure:

Orders

ID	Amount
AA	12
AA	88
BB	34
CC	90
CC	56
DD	23

History

ID	Status	EditDate
AA	Ok	23/09/2025
AA	Delayed	14/03/2025
BB	In progress	15/04/2025
BB	Ok	21/01/2025
CC	In progress	29/06/2025
CC	Failed	31/12/2025
CC	Ok	01/12/2023
DD	Delayed	03/03/2024
DD	Contact	11/12/2020

I want to create a column in the "Orders" table that retrieves the value of the "Status" column in the "History" table (through the ID relationship), but only the status where the EditDate column is the most recent date for that specifi ID. The end result would be something like this:

ID	Amount	Status
AA	12	Ok
AA	88	Ok
BB	34	In progress
CC	90	In progress
CC	56	Failed
DD	23	Delayed

How can I do this?

[Ashish_Mathur]

Hi,

This calculated column formula works

=LOOKUPVALUE(History[Status],History[EditDate],CALCULATE(MAX(History[EditDate]),FILTER(History,History[ID]=EARLIER(Orders[ID]))),History[ID],Orders[ID])

Hope this helps.

[Nabha-Ahmed]

Hi @nok 

You can do this easily using DAX by creating a calculated column in your Orders table that looks up the most recent status from the History table for each ID.

Here’s how:

Status =
VAR currentID = Orders[ID]
VAR LatestDate =
CALCULATE (
MAX ( History[EditDate] ),
FILTER ( History, History[ID] = currentID )
)
RETURN
CALCULATE (
SELECTEDVALUE ( History[Status] ),
FILTER (
History,
History[ID] = currentID &&
History[EditDate] = LatestDate
)
)

  
Thanks✨🌹

[Shahid12523]

LatestStatus =
VAR CurrentID = Orders[ID]
VAR LatestDate =
CALCULATE(
MAX(History[EditDate]),
History[ID] = CurrentID
)
RETURN
CALCULATE(
MAX(History[Status]),
History[ID] = CurrentID,
History[EditDate] = LatestDate
)

[kpost]

1) Open Power Query, pull up your History table. Make sure that "ID" is formatted as text and the "Date" column is formatted as date.

2) Duplicate the History Table.  Call it something descriptive like "History_Maxdate".  Immediately un-select "Enable Load", as you will not be integrating this table into your semantic model.

3) Remove the "Status column" from your new History_Maxdate table.

4) Do a "Group by" on your History_Maxdate table and group by the ID, using MAX(Date) as the grouping.

5) You should now have a table with Unique IDs in the first column and the "latest" of the dates for each one in the second column.

6) Now you want to do a "Merge" between this "History Maxdate" table and your "History" table, like so, selecting both the ID and the Date columns.  

7) Now you want to "Expand" that new column, selecting only the "status" field.

8 ) ou should now have all of the correct Status values for the latest date.

9) Next you want to do another 'Merge', this time from your Order table to this History Maxdate table, only on the ID column.

10) Expand the new column by bringing in only the 'Status', and voila you are done.

'

See the attached .pbix file with your solution implemented.  And if this solution solves your problem for you, please mark 'Accept as Solution!'  Thank you.

(BTW, I think in your desired result table you had a typo?  It should say "Failed" for both CC orders instead of "In Progress" for one and "Failed" for the other. 

///Mediocre Power BI Advice, but it's free!///