Another interesting question that came up recently is how to do currency conversion in Power BI. My colleague Jeffrey Wang had a very neat solution that I want to share more broadly.
Imagine you have sales in 3 different currencies happening on different dates:
Now when you are doing reporting you want to report them all in a single currency. So the first thing we need is to get a table with conversion rates for each date for each currency:
Next we need to model this so in the report we can choose which currency we want to report on and then automatically apply the right conversion rate. The model looks like this:
Both the Sales and the Exchange rate table are fact tables so we need to create 3 dimension tables to tie them together. I use calculated tables to create them as the data is already there:
ExchangeDate = Distinct(FactExchangeRate[Date]).
ReportCurrency = Distinct(FactExchangeRate[ReportCurrency])
TransactionCurrency = Distinct(FactExchangeRate[TransactionCurrency])
The ReportCurrency table will be used to create the slicer in the report that allows us to select the currency. The other 2 tables will allow us to get the correct Exchange rate for the each sales transaction using date and currency. This means that for each row in the factsales tables we filter down to 3 rows in the FactExchangerate table based on the date and currency relationship. Now based on the ReportCurrency slicer we filter it down to a single row thus leaving us with the Exchange rate we need. The problem here of course is that we need to do this on the fly because for each day we might have transactions using multiple currencies so we need to take that into account. Make sure you don’t use the currency and dates from the fact tables in your visual, pick the values from the dimensions otherwise the filters will not be applied correctly.
We can do that using DAX, I start with getting the factor to calculate the exchange rate:
TrnExchangeRate = Min(FactExchangeRate[Factor])
This will get the lowest factor value from the fact table.
Now to calculate the sales per transaction I will use SUMX to achieve this:
TotalSales = if(HASONEVALUE(ReportCurrency[ReportCurrency]),
SumX( FactSales, [Sales] * [TrnExchangeRate]))
This DAX expression will summarize (SUMX) each row in the FactSales table (that is in the current selection) . For each row it will use the value from the sales column times the Exchange rate returned from the TrnExchangeRate measure. The SUMX is key to get the right numbers, it will iterate over the rows in the fact table dynamically and summarize the results into a single number (currency). Again this is key as each row might be a different currency on a different date and the relationships will make sure only a single value will get returned from the FactExchangeRate.
Putting them all together it allows me to build a report like this:
As you can see the 100 Euro’s get translated to 117.44 USD where the USD amounts stay the same. So a clever combination of the model and some DAX will get you currency conversion. Also the grand total is showing the one USD sales number. Unfortunately there is dynamic formatting yet in Power BI to apply the format on the fly as well.
You can download the sample file here.
very clever, he uses TrnExchangeRate = Min(FactExchangeRate[Factor]) as calc measure to achieve the rate by its filter context and multiply with current sales value by sumx ^_^ really surprise with the way using sumx
good day
is it the same thing if make relationship between ‘FactSales’ and ‘FactExchangeRate’
based on [Date]&[TransactionCurrency]
and measure like
TotalSales = IF (
HASONEVALUE ( FactExchangeRate[ReportCurrency] ),
SUMX ( ‘FactSales’, [Sales] * CALCULATE ( MIN ( ‘FactExchangeRate'[Factor] ) ) )
)
?
or it will have performance issues?
thank you!
That will give you a direct many to many which isn’t supported, you need to go through intermediate tables. The measure will probably work but I always would recommend splitting them up for debug and cleanliness sake :).
Before calculated tables, I think we did something general such as:
SUMX(
CROSSJOIN(Currency, Date)
CALCULATE(VALUES(ExchRates[Factor]) * Sales[SalesAmt],
Filter(Values(ExchRates[DateID]),
ExchRates[DateID] = MAX(ExchRates[DateID])
)
)
)
I am so glad variables and calculated tables have come along.
I like the additional twist you put on this while maintaining leaf level calculations.
@Kasper.
How are the filters propagating in the measure TotalSales
Hi!
I wonder how would one configure the data model if, the factSales table would have a date range where the sales were valid ( lets say 2 fields [dateFROM] and [dateTO] ), and the convertion rate table also with 2 columns of the same type.
How would you do this?
Thank you. 🙂
I would tweak the sumX to iterate on the distinct combination of sales date and transaction currency only rowstime and not per each sales fact row. It will perforn way better – need just to use sumx over summarize function.
Can you please give example DAX expression how did you accomplish sumx over summarize function.I am very interested to know how to accomplish SumX to iterate on the distinct combination of sales date and transaction currency only rows time and not per each sales fact row
Hello Kasper,
Thanks for writing this blog.
I have one question:
If my FactExchangeRate table contains data in format
FromCurrency, ToCurrency, ExchangeRate, ValidFrom and ValidTo. So can we write DAX to get ExchangeRate dynamically for each FactSales record.
I tried writing DAX but unable to get the desired output:
ExchangeRate = CALCULATE(MIN(ExchangeRate[ExcahngeRate]), FILTER(ExchangeRate, AND(AND(AND(MIN(FactSales[TransDate])>=MIN(ExchangeRate[ValidFrom]), MIN(FactSales[TransDate])<=IF(ISBLANK(MIN(ExchangeRate[ValidTo])), DATEVALUE("01-01-2070"), MIN(ExchangeRate[ValidTo]))), MIN(ExchangeRate[FromCurrencyCode]) = MIN(FactSales[CurrencyCode])), MIN(ExchangeRate[ToCurrencyCode]) = FIRSTNONBLANK(ExchangeRate[ToCurrencyCode],1))))
https://uploads.disquscdn.com/images/75b370e34ccb8f85f16b849a3e86fb2b44b4608db119ec5b199cddbe881cd94c.png https://uploads.disquscdn.com/images/69a081bb26c2e12e8ce20a298fbe0f35d772b6f3f453efc506cb2b06cbb704ff.png
Able to write to calculate Exchange Rate dynamic:
ExchangeRate =
IF(ISBLANK
(
CALCULATE(MIN(ExchangeRate[ExcahngeRate]),
FILTER(FILTER(ExchangeRate, AND(AND(ExchangeRate[FromCurrencyCode] = MIN(FactSales[TransactionCurrencyCode]), ExchangeRate[ToCurrencyCode] = FIRSTNONBLANK(ExchangeRate[ToCurrencyCode],1))
, MIN(FactSales[TransDate])= ExchangeRate[ValidFrom]))
)
, 1
, CALCULATE(MIN(ExchangeRate[ExcahngeRate]),
FILTER(FILTER(ExchangeRate, AND(AND(ExchangeRate[FromCurrencyCode] = MIN(FactSales[TransactionCurrencyCode])
, ExchangeRate[ToCurrencyCode] = FIRSTNONBLANK(ExchangeRate[ToCurrencyCode],1))
, MIN(FactSales[TransDate])= ExchangeRate[ValidFrom]))
)
I really like how you explained this in a simple yet effective manner. But being a newbie, I still struggle translating this into my case, which is very similar.
The difference is the user selects the CurrencyDate (instead of the target currency), and the target currency is always USD.
I created intermediate tables and relationships linking the fact and rate tables similar to yours, but CurrencyDate is the calculated table used as the slicer.
The calculated measures on the fact look like this:
TrnExchangeRate = Min(FactExchangeRate[Factor])
AmountUSDAtDate:=
if(HASONEVALUE(CurrencyDate[CurrencyDate]),
SumX( FactSales, [Sales] * [TrnExchangeRate]))
I’m not sure where to put the restriction on USD and I get empty values for the AmountUSDAtDate calculated measure. Could you please help me understand why?
Hi @Jean, Did you get the solution for your problem.. If not..? try this
TrnExchangeRate= if(isblank(Min(FactExchangeRate[Factor]),1,Min(FactExchangeRate[Factor]))..
Hello,
I use the above logic to my model. As I have millions of data, there is performance issue.
I use the below dax, but the “[TrnExchangeRate]” is not working here.
How can I overcome this issue?
TotalSalesNew =
var summarizetable = SUMMARIZE(FactSales,FactSales[CURRENCY],FactSales[Date],”total_sales”,SUM(FactSales[Sales]))
return
SUMX(summarizetable,[total_sales])*[TrnExchangeRate]
I don’t see how this can work. Surely the filter direction between the SalesFact > exchange Date > FactExchangeRate means that when you use the min(TrnExchangeRate) it won’t be filtere by date. This will result in getting the min exchange rate between currency pairs irrespective of the date of the Sales record.
It depends how you construct your measure. Kasper made the TotalSales with SUMX. In this case works. If you would use simple SUM then you are right it does not work.
Hi, thanks for the clear example! I’m managing to get this to work in my own spreadsheet. I however, have some issue with doing a running total on the transactions (Sales in your example). The issue is that the running total will work with each individual currency, but converting this, it will do this for each individual transaction, meaning that the running total will be ‘off’ after some time.
The desired outcome would be that the running total would go and sum all the transactions per month / timeperiod, and then at the end of each month / time period , convert this to the ReportCurrency. Would you have a solution for such a problem?
The running total is coded as such:
Running Total ORIG CURR =
CALCULATE(
SUM(‘All Transactions'[Credit Amount]) – SUM(‘All Transactions'[Debit Amount]),
FILTER(ALL(‘Date’),
‘Date'[Date] <= MAX('Date'[Date])
)
)
Using this in a SUMX from your example did not work.
Hi. If I want to calculate month on month Actuals Value based on Exchange rate’s closing date so how can that be achieved?
As of now I have tried to get the exchange rate based on closing date and also dividing it by that rate but I am not getting the value which I get in manual calculation on excel sheet. Can anyone please help me?