Get latest updated currency rate

[Markando]

Hello!

I've got a sales tables with transactions spanning every weekday. The sales transaction is logged in local currency (EUR) but I want to convert the report to USD. My currencyrate table is however only updated on saturdays. So I would like get the latest available currencyrate to calculate my report with. Any good ideas?

For the example below I would like to get 1,18 and 1,2 for the two different transactions and sum up the total

Sales:

Date | Salesamount | Currency

2017-08-28 | 50 | EUR

2017-09-04 | 100 | EUR

Currencyrates

Date | Currency | Rate

2017-08-26 | EUR/USD | 1,18

2017-09-02 | EUR/USD | 1,2

[v-ljerr-msft · Accepted solution]

Hi @Markando,

Based on my test, you should be able to use the formula below to add a calculate column in Sales table to get the latest available currencyrate, and then use it in other calculations. 

Rate = 
CALCULATE (
    MAX ( Currencyrates[Rate] ),
    FILTER ( Currencyrates, Currencyrates[Date] <= Sales[Date] )
)

SalesamountUSD = Sales[Salesamount]*Sales[Rate]

Regards