Lookup nearest date from another table

[Paal]

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.

[EricHulshof · Accepted solution]

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

[Paal]

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