cancel
Showing results for
Did you mean:
Highlighted
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

## 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.

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

Regards

Highlighted
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.

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

Regards

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors