Fetch value from another table where date is more recent

Hi nok 

Load both tables into Power Query.
In the History table:

Sort by EditDate descending.
Use "Remove Duplicates" on the ID column — this keeps the row with the most recent date.

Now, go to the Orders table:

Use "Merge Queries" to join with the filtered History table on ID.
Expand the Status column from the merged table.

This will give you the desired result.

--------------------------------

I hope this helps, please give kudos and mark as solved if it does!

Connect with me on LinkedIn.

Subscribe to my YouTube channel for Fabric/Power Platform related content!

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!///

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
)

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✨🌹

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.

Hi nok 

You can use TOPN (top 1) on history table filtered by order ID and sorted by Edit Date in descending order and then return the status

Latest Status = 
VAR _ID = Orders[ID]
VAR _History =
    TOPN ( 1, FILTER ( History, History[ID] = _ID ), History[EditDate], DESC )
RETURN
    MAXX ( _History, [Status] )