Sale-days exchange rates stay beside the sales in Power BI

[Anonymous]

I have a table (data source) with exchange rates uppdate daily for USD and EUR.

I also have a sales table with sails records (can be USD or/and EUR) from day to day.

After having a sale on that day, I want that day's exchange rate record (from the dynamic exchange rate table) stay beside the sale and remain as a static value.

Example:

Sales day 2018-12-12, amount in EUR 100, exchange rate EUR/USD = 1.1221

Sales day 2018-12-14, amount in EUR 100, exchange rate EUR/USD = 1.1331 (this rate changes in the look up table)

I would like my sales table show as:

Sales-day, SalesAmountLocalCurrency, ExchangeRate, SalesAmountInDollar

2018-12-12, 100 EUR, 1.1221, 112.21 USD

2018-12-14, 100 EUR, 1.1331, 113,31 USD

How should I work out in Power BI?

Any helps are appreciated. Thanks in advance.

/Dav

[LivioLanzo]

Hi @Anonymous

if you have a large fact table I would suggest you not to add all these extra columns in the fact table. The amount in different currencies can be calculated on the fly by a measure. If you have just two currencies then for simplicity you could add an amount in USD and one in EUR in the fact table but you do not need to also add the FX rate. In such a case then you wouldn't need to import the FX rates table anymore. This can be done with a few joins either in Power Query or via a custom view in your database 

[Anonymous]

Thanks LivioLanzo,

First I do not have a large fact table (but it can be in the future, who knows). Could you please give more tips on how the joins in Power query or custom view.

The fact tabel has a Sales date, the amount for that day and in local currency, currency code.

The exchange rate table has Currency code, exchange rate date, and the rates.

I have a relation for these two table on the date, but once the exchange rate changes next day so wouldn't my sales also change? But it should be fixed by that sales days exchange rate. Or have I missed something?

Tanks again, and looking forward for clearification. 🙂

/dav

[LivioLanzo]

@Anonymous

check the file: https://1drv.ms/u/s!AiiWkkwHZChHj02hsQvqYu_3yGVf

[Anonymous]

Thanks very again LivioLanzo,

I really appreciate your effort and enthusiast to help me. I still have the problem which I can only access the live exchange rate table and NOT the historical rates. How should I do then? So the first step of the solution is to create a history rates table. Once I have the hist-rate-table, your model/solution will be the final step to solve my problem.

So, how should I create this hist-rate-table from a live-rate-table which changes values Daily? The hist-rate-table should be daily appended with the Date, CurrencyKey and the Rate, in a format shown in your FXRates. Is it possible in Power BI?

/Dav 🙂

[LivioLanzo]

Where are you sourcing the fx rate from?

[Anonymous]

I don't have the FX rate. I only can access a dynamic rate table from Microsoft dynamics 365 cloud server. And it changes daily.

I can also get the same rate from "= Web.Page(Web.Contents("http://www.xe.com/currencytables/")) "

[LivioLanzo]

Hi @Anonymous

You could find a website which has historical FX rates.

You could set up a script which runs daily and stores the rate in a database.

If you have premium you can set up incremental refresh with Dataflows. 

The dynamics server does not have historical data?

[Anonymous · Accepted solution]

Hi LivioLanzo,

Thanks for your tips. I will do that.

I am not sure if the dynamics server has a FX rate table. I will research it.

Once I have that, I am sure your solution will work!

Thanks again for help!

Best wishes.

/Dav 🙂