Integrating current exchange rate on Power BI

[gerget]

Dear all,

I would like to integrate current exchange rate conversion in Power BI.

I have sales data on EUR and TNE currency. My objective is to convert TNE to EUR based on the exchange rate of the particular date.

so, that the sales amount will be accurate based on the transaction date in EUR.

I already create the dashboard which is automatically refreshed. But dut the fixed exchange rate the sales amount is not accurate.

Thanks in advance.

Best regards,

Gerget

[v-lili6-msft]

hi, @gerget

After my research, you could try this way:

Step1:

Get data from a current exchange rate website. The column must include ( Date, current, rate) like this:

Note: this is just virtual data and website is chosen by you.

Step2:

Add a Ismaxdate column for current exchange rate table.

Ismaxdate = IF('current exchange rate'[Date]= CALCULATE(MAX('current exchange rate'[Date]),FILTER('current exchange rate','current exchange rate'[Currency]=EARLIER('current exchange rate'[Currency] ))),"yes","no")

Note: if you get the data that date is beyond today, you could filter date that is before today in Edit Queries.

Step3:

Add a lastest current exchange rate for data table by this formula

lastest current exchange rate = CALCULATE(MAX('current exchange rate'[Rate]),FILTER('current exchange rate','current exchange rate'[Currency]='Table'[Currency]&&'current exchange rate'[Ismaxdate]="yes"))

Step4:

Now you could add a new value column

NEW Value = 'Table'[Value]*'Table'[lastest current exchange rate]

Step5:

You could set schedule refresh for current exchange rate table and when it refresh completed, the result will be refresh too.

Best Regards,

Lin

[Omega]

Since the exchange rate keeps changing by date, create a table with date and new exchange rate columns. Connect your new table with the original table using date as a key. Then, create measure to multiply your sale with the exchange table in the new table. 

[gerget]

Dear Omega,

Is there any method to get the exchange rate daily from any website that can be integrated on Power BI? So that the dashboard created on the Power BI service can be automatically refreshed with the new exchange rate.

My problem was, I have a date column and sales amount Column in TNE currency. Based on the date, new rows are calculated with the new exchange rate.

Thank you,

Best Regards,

Gerget

[ReneeGenData]

Did you ever find a solution to your question?

[Omega]

Sorry, but I'm not a finance expert. One way is to connect to a data source that has the exchange rate, which you need to find, and in the Power BI service, you can schedule a daily refresh to pull the latest data from exchange rate source.