Currency Conversion using historical Conversion Table

[Anonymous]

Good evening Community!

I have a problem that I am finding difficult to solve.  I have read and read and read, but I can't not find a solution that works.  I am trying to do Currency Conversion for all sales of the last X years, (X can change), but there are two major considerations.  1) All data is historical, therefore has to convert into the chosen Currency based on the rate at the time of Transaction. and 2) Due to the complicated nature of the rest of my model, none of my relationships to my main dimDate table can be active.  They all have to be inactive. (i have been using USERELATIONSHIP in almost every measure dependant on date with great success)

I have a Sales table with all values already converted to USD. (Sample Below)

Sales_in_USD	Transaction Date
100	1/1/2020
42.4	3/5/2020
4350	1/5/2021

I also have an Exchange Rate table with the Currency, ExchangeDate and Rate. (Sample Below)

CurrencyCode	ExchangeDate	ExchangeRate
EUR	1/1/2020	0.89
EUR	3/5/2020	0.88
EUR	1/5/2021	0.9
AUD	1/1/2020	1.2
AUD	3/5/2020	1.2
AUD	1/5/2021	1.1
CAD	1/1/2020	1.11
CAD	3/5/2020	1.13
CAD	1/5/2021	1.2
GBP	1/1/2020	0.9
GBP	3/5/2020	0.97
GBP	1/5/2021	0.91

What im looking to do is using a filter or slicer, allow the user to select one Currency and have all the totals convert to that currency for the time frame selected.  This will more than likely be a total number in a card, not really shown as a table. However, as mentioned before, that Sum needs to take into consideration the dates at which the transaction took place in order to convert correctly.   The ideal result would be like this....

If the User chose (Currency) it would show (Total)

EUR	4041.312
AUD	4955.88
CAD	5378.912
GBP	4089.628

Any help is greatly appreciated.  Please let me know if I need to explain any parts further (i tend to struggle on that part)

Thanks!

[Anonymous]

Hi @parry2k ,

This is great. It solved 60% of my promblem. 
Just wanted to ask what is the solution is i have 30 - 35 columns. The scalability is the issue for me if the number of columns increases. 

Could you suggest any solution. 

[parry2k · Accepted solution]

@Anonymous have relationship between these two tables on Transaction Date , it will be many to many, on relationship page set relation that "Sales filter Currency table"

and now add the following measure

SUMX ( SalesTable, CALCULATE ( SUM ( SalesTable[Amount] ) ) * CALCULATE ( MAX ( ExchangeTable[ExchangeRate] ) 

add slicer on Currency code and have it single select so that a currency is selected.

Use this new measure to get sales in the selected currency.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

That did the trick!  I think i was too wrapped up in the dimDate table.

Thank you so much!