cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

Lookup nearest date from another table

I have a table Enquiries with one column called EnquiryDate and another column called OrderDate.  For the enquiries that turn into Orders, each row contains a currency (EUR, USD, ...) and an Order Amount (in the local currency).

 

I have another table, FXrates, which holds daily observations (almost..) of exchange rates.  For each of the orders in the Enquiries table, I want to lookup the appropriate exchange rate. The challenge is that the exchange rate observations are not on all dates, such as Sundays and holidays. So for those OrderDates that don't have an exact match in FXrates[fxDate], I want to pick up the closest date. In principle, I should like to collect the 5 days before and 5 days following the OrderDate, and pick the date which is closest to OrderDate.  (First go one day back , then one day forward.)

 

I've search around but haven't quite been able to get there, which is partly due to my own lack of experience.  Based on one post somewhere I have tried the following calculated column:

 

=
VAR ClosestDateBefore = CALCULATE(
                              MAX( FXrates[fxDate] ),
                              FILTER( CALCULATETABLE(VALUES(FXrates)),                                                                    
                                 AND(FXrates[fxDate]>DATEADD(Enquiries[OrderDate]; -5; DAY), FXrates[fxDate]<=Enquiries[OrderDate])
                                     )
                              )
RETURN DATEDIFF(ClosestDateBefore, OrderDate, DAY)

 

..as a start, the idea being to try a step-wise approach, first finding the closest day before OrderDate, then the closest day after OrderDate, etc. - but it doesn't seem to be quite right and I'm a bit out my depth on this.  

 

Thanks in advance.

1 ACCEPTED SOLUTION
Solution Sage
Solution Sage

Well i actually fixed a similar thing this week! Not with dates but who cares hu? 😛

I used this formula for closest LOWER
 

VAR lookupinlaattemplower = CALCULATE(
    MAX('Isentrope coefficient(k)'[Temp(k)]);
    FILTER(ALL('Isentrope coefficient(k)'[Temp(k)]);'Isentrope coefficient(k)'[Temp(k)] < inlaattemp))

Where MAX(coefficient temp (k)) is the value i want.
I filter all temp(k)'s if the temp(k) is lower than my temperature i already know (inlaattemp)
This way it will allways return the value before the known value.

In your case it would be something like this:

VAR lookup_DATE_lower = CALCULATE(
    MAX(FXratesDATE);
    FILTER(ALL(FXratesDATE);FXratesDATE <= EnquiriesOrderDate))


i added an = because if it does exists you want the correct date.

My original topic can be found here:

https://community.powerbi.com/t5/Desktop/Double-lookup-for-value/m-p/908215#M435294


Quality over Quantity


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


View solution in original post

2 REPLIES 2
Solution Sage
Solution Sage

Well i actually fixed a similar thing this week! Not with dates but who cares hu? 😛

I used this formula for closest LOWER
 

VAR lookupinlaattemplower = CALCULATE(
    MAX('Isentrope coefficient(k)'[Temp(k)]);
    FILTER(ALL('Isentrope coefficient(k)'[Temp(k)]);'Isentrope coefficient(k)'[Temp(k)] < inlaattemp))

Where MAX(coefficient temp (k)) is the value i want.
I filter all temp(k)'s if the temp(k) is lower than my temperature i already know (inlaattemp)
This way it will allways return the value before the known value.

In your case it would be something like this:

VAR lookup_DATE_lower = CALCULATE(
    MAX(FXratesDATE);
    FILTER(ALL(FXratesDATE);FXratesDATE <= EnquiriesOrderDate))


i added an = because if it does exists you want the correct date.

My original topic can be found here:

https://community.powerbi.com/t5/Desktop/Double-lookup-for-value/m-p/908215#M435294


Quality over Quantity


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


View solution in original post

@EricHulshof 

That was a fairly clear and simple approach which was very helpful. And a reminder to try to think one step at a time to avoid over-complicating...!  Thanks for your help!

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors