cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Get latest updated currency rate

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



1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Get latest updated currency rate

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. Smiley Happy

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

c2.PNG

 

Regards

View solution in original post

1 REPLY 1
Highlighted
Microsoft
Microsoft

Re: Get latest updated currency rate

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. Smiley Happy

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

c2.PNG

 

Regards

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors