Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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.
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
That did the trick! I think i was too wrapped up in the dimDate table.
Thank you so much!
User | Count |
---|---|
98 | |
90 | |
84 | |
70 | |
66 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |