Forum Discussion

nok's avatar
nok
Icon for Advocate II rankAdvocate II
11 months ago
Solved

Fetch value from another table where date is more recent

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/0...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-dineshya's avatar
    v-dineshya
    11 months ago

    Hi nok ,

    Thank you for reaching out to the Microsoft Community Forum.

     

    Please refer below Calculated column.

     

    Status =
    VAR latestDate =
        CALCULATE(
            MAX(History[EditDate]),
            FILTER(History, History[ID] = Orders[ID])
        )
    RETURN
        CALCULATE(
            MAX(History[Status]),
            FILTER(
                History,
                History[ID] = Orders[ID] &&
                History[EditDate] = latestDate
            )
        )
     
    Please refer output snap and attched PBIX file.
     

     

     

    I hope this information helps. Please do let us know if you have any further queries.

     

    Regards,

    Dinesh

     

     

kpost's avatar
kpost
Icon for Solution Sage rankSolution Sage
11 months ago

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