To return value based on start and end date column with condition.

[New_hello188]

Hello All , 

I need to refer to table B and get the appropriate exchange rate for a transaction based on the posting date (table A).  Ignore the currency code as report need to be presented in SGD value. 

condition 1 : If the posting date falls between the starting date and ending date specified in Table B, then the corresponding exchange rate in Table A should be used for that transaction.

condition 2 : If there is no ending date specified in Table B and the posting date is after the starting date, then the exchange rate to be applied should be the last row of exchange rates listed in Table B.

Is there any method to meet this condition ? Thanks ! 

[amitchandak]

@New_hello188 , A new column in table 2

Maxx(filter(Table2, Table1[Currentcy Code] = table2[currency code] && Table1[Date] >= Table2[Start Date] &&( isblank(Table2[End Date]) || Table1[Date] <= Table2[End Date]) ) , Table2[Exchnage_Rate])

refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[New_hello188]

Thank you ! 

Could you please guide , how can I also apply filter at the currency code at Table B ? 

The actual data in Table B consists of lots of different currency code and I only need SGD rate return to Table A even though the currency code is in USD. 

I tried to modify your measure to meet above condition , but it was not working. 

Maxx(filter(Table2, Table1[Currentcy Code] = table2[currency code] = "SGD" && Table1[Date] >= Table2[Start Date] &&( isblank(Table2[End Date]) || Table1[Date] <= Table2[End Date]) ) , Table2[Exchnage_Rate])