how to match dates and find closest ones

[Anonymous]

Hello all,

May I kindly ask you for your input? I have the table with exchange rates which are used for conversion from different currencies to USD. but I do not have all dates for conversion for a particular amount. So for example the FX rates table is missing the date 01/01/2022 so the conversion was not done for the amount that was created on 01/01/2022. How can I find the nearest date and apply conversion for that amount for example for a rate from  01/02/2022 and at the same time I want to match dates and rates for matching dates?

Something like this: 

 if "exchange rate date" = "create date" then "exchange rate" else "find the nearest date to the create date from exchange rate" 

Hope this is clear, if opposite, I am happy to explain it another way. 

Thank you in advance.

Katarina 

[Manoj_Nair]

@Anonymous - It's a very genuine use case.

Tried to stimulate the tables i.e FX & invoice with fictitious data for calculation purposes. If the below screenshot meets your requirement, I will post the PBIX file. Thanks

[Anonymous]

Hello, thank you for your reply, I would need to do some power query (some formula) in Transform data I noticed that the missing dates are weekend days so I would need to have if the date falls on Saturday or Sunday then take the Friday. 

Sorry for the confusion. 

Thank you 

Katarina

[Anonymous]

Hello,

thank you for your support, but this will not work for me as the data are from SharePoint and the dashboard is updating each day. I would need some formula that can consider it each time when the date from the FX rate table is missing. Therefore I need to find a solution with the closest date.

Thank you 

Katarina

[v-yalanwu-msft]

Hi, @Anonymous ;

The premise is that you need to have a table, the table is the exchange rate corresponding to each date, and secondly, you can match the corresponding exchange rate according to this table.
Or you can use conditional formatting

if [date] = a certain day then [value]* exchange rate else
if date] = other day then [value]* exchange rate else...

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.