A really common scenario within financial analysis is having to integrate currency rates across your transactional data.
Most of the time, sales data is actually recorded regardless of the currency and the currency name or code is just placed inside a column. And it could be either USD, AUD, GBP, but it isn’t actually adjusted by the currency rate for you.
So what you have to do is integrate a whole lot of historical exchange rates, because through time, exchange rates change. And so depending on the day that you sold something, the exchange rate equivalent is going to be very different.
I’m going to show you here how you can actually solve this inside of Power BI.
There’s a little bit that you have to understand, especially around the data model which I know is a relatively new concept to a lot of people. But I show you a lot of my best practices here and then how to ultimately connect or how to bring in this exchange rate information into your sales or your transactional table.
Now if you do this effectively, it becomes very seamless to do these calculations. By understanding how to sort the data model and also how to utilize the DAX measures LOOKUPVALUE, you have the ability to very easily run currency adjusted calculations and not only that, you can also dynamically look at your results depending on what currency you want to look at it in.
So lots of applications for this, especially in the financial world…or especially for an organization which sells goods across many different currencies. Very applicable to a real-world situation. Hopefully this helps those who run into this problem and are looking for a solution.
Good luck with implementing this technique yourself.