Lookup for a value on date condition in another table and create a new column in original

[mahadevshindhe]

Hi All,

I am looking for a solution where need to convert currency based on the exchange rate. The exchange rate is provided in another table with rate and valid from date.

Source Table:

Amount (USD)     Posting date
120                      15-07-2022
560                      15-05-2022
456                      10-02-2022

Lookup Table:

MYR  Exchange rate          Valid from
2.256                                  10-01-2022
4.433                                  12-03-2022
3.434                                  14-05-2022
4.456                                  12-06-2022

Based on the "Posting date" from the source table, I need to look for the exchange rate and its "Valid From" date then mulitply the value with Amount(USD) from the source table to create Amount(MYR) column in source

Final Table:

Amount (USD)   Posting date    Amount(MYR)
120                    15-07-2022      534.72
560                    15-05-2022      1923.04
456                    10-02-2022      1028.736

[Amol_Telore]

Hey @mahadevshindhe 

Here is my solution.

[Emmanuel_G · Accepted solution]

Hi @mahadevshindhe 

Do you want something like this ?

PS : The date condition is included inside the filter tool.