cancel
Showing results for
Did you mean:
Frequent Visitor

## Convert currency

I need to convert the 'amount' column in the below table to EUR (currency is GBP at the moment), and then to merge the table with another that is already in EURO.

So far I have not been able to do this without returning errors.

I have a currency conversion table (also below) that shows todays exchange rate, however I am unsure how to use the table to write a formula that will convert the amount.? Any help would be appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Convert currency

This will work if you get rid of all of your non-GBP exchange rates. Do you need those? If you do, sure some more DAX magic can get you there.

`Column = LOOKUPVALUE(ExchangeRates[ExchangeRate],ExchangeRates[CurrencyCode],"GBP",ExchangeRates[StartTime],MAX(ExchangeRates[StartTime]))`

Oh wait, here is the additional DAX magic that will let you keep your other currency codes.

`Column = LOOKUPVALUE(ExchangeRates[ExchangeRate],ExchangeRates[CurrencyCode],"GBP",ExchangeRates[StartTime],CALCULATE(MAX(ExchangeRates[StartTime]),ExchangeRates[CurrencyCode]="GBP"))`

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

9 REPLIES 9
Super User

## Re: Convert currency

This will work if you get rid of all of your non-GBP exchange rates. Do you need those? If you do, sure some more DAX magic can get you there.

`Column = LOOKUPVALUE(ExchangeRates[ExchangeRate],ExchangeRates[CurrencyCode],"GBP",ExchangeRates[StartTime],MAX(ExchangeRates[StartTime]))`

Oh wait, here is the additional DAX magic that will let you keep your other currency codes.

`Column = LOOKUPVALUE(ExchangeRates[ExchangeRate],ExchangeRates[CurrencyCode],"GBP",ExchangeRates[StartTime],CALCULATE(MAX(ExchangeRates[StartTime]),ExchangeRates[CurrencyCode]="GBP"))`

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

Super Contributor

## Re: Convert currency

@sgannon1

The solution provided by @Greg_Deckler should work. And you can also try with following column formula.

```Converted Amount_Column =
VAR LatestDate =
CALCULATE (
MAX ( 'Currency Conversion'[Starting Date] ),
'Currency Conversion'[Currency Code] = "GBP"
)
RETURN
(
CALCULATE (
VALUES ( 'Currency Conversion'[1.2 Exchange Rate Amount] ),
FILTER (
'Currency Conversion',
'Currency Conversion'[Currency Code] = "GBP"
&& 'Currency Conversion'[Starting Date] = LatestDate
)
)
* Table1[1.2 Amount]
)```

Best Regards,
Herbert

Highlighted
Member

## Re: Convert currency

I'm struggling with a solution that converts each invoce to another currency based on the invoice date. I just can not find a solution here in the Community for that approach.

Thanks,

Fernando

Super User

## Re: Convert currency

@calerof - Can you provide example/sample source data and expected output?

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

Member

## Re: Convert currency

Hello @Greg_Deckler,

Sorry for not answering before, my client started requesting things afar from the agreement.

The data are as follows:

Fact Table with sales by invoice line:

https://www.dropbox.com/s/hz5nc6kqda4wbv9/Daily%20Revenue%20by%20invoice%20item%20Original.xlsx?dl=0

 Date USD 01-ene-19 19.6512 02-ene-19 19.6512 03-ene-19 19.5878 04-ene-19 19.6073 05-ene-19 19.6073 06-ene-19 19.6073 07-ene-19 19.4902 08-ene-19 19.3479 09-ene-19 19.3479 10-ene-19 19.2456 11-ene-19 19.1236 12-ene-19 19.1236 13-ene-19 19.1236 14-ene-19 19.1236 15-ene-19 19.0064

In excel I would do it just easily with =vlookup convert all sales in MXP to USD based on the invoice date and bring the sales already in USD to the same column to have a total in USD, e.g.

https://www.dropbox.com/s/jvwex5u8o0l7mo9/Daily%20Revenue%20by%20invoice%20item.xlsx?dl=0

In this way the last column would show Sales in USD converted at the invoice date, not at month end.

When I use LOOUPVALUE my problem is when assigning the current date in SEARCH VALUE doesn't recognize the row context and get the error: "A table of multiple values was supplied where a single value was expected".

LOOKUPVALUE (
EXCHANGERATES[RATE],
EXCHANGERATES[DATE], DATE ( 2019, 1, 12 ),
EXCHANGERATES[CURRENCY], "MXP"
)

Thanks a lot,

Fernando

Member

## Re: Convert currency

So as you can see, my intent here is to be able to convert each individual invoice based on its date's exchange rate, not one final convertion at month end with only one exchange rate.

Could you advise on a way to do it?

Fernando

Member

## Re: Convert currency

Hello @Greg_Deckler,

Sorry to insist. I'd just only like to know if there is a way I can solve my problem of currency conversion in Power BI.

Thanks and regards,

Fernando

Member

## Re: Convert currency

Finally with the help of @EnterpriseDNA's video I could have the sales data converted from MXP to USD with the following code:

```Sales in USD = SUMX( NetSales,
DIVIDE(
NetSales[LineTotal],
LOOKUPVALUE( ORTT[Rate],
ORTT[RateDate], VentasNetas[OINV.DocDate],
ORTT[Currency], "USD" )
)
)```

where:

NetSales in the sales fact table,

ORTT includes the MXP/USD exchange rate by date.

I post this if it could be of any help.

Regards,

Fernando

Regular Visitor

## Re: Convert currency

I've a similar problem, in my case with nominal to constant currency....
anyway, in the proposed solution there are using the latest available exchange rate (MAX(StartTime))... i'm using it and it works fine, but can I make an slicer or use another pickable menu to change the date of the exchange rate used? What if I wish to know the price in GBP of March 1 2017?

Thanks

Announcements

#### Community Highlights

Find out what's new in the Power BI Community!

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 378 members 4,081 guests
Recent signins: