If you’re selling globally, integrating multiple currency rates into your transactional data is a common scenario within your financial analysis. I get a lot of questions in the Enterprise DNA Support Forum from accountants and finance people who want to work out their sales that are valued in their home currency.
Sales data is always recorded regardless of the currency and the currency code or name. It could be USD, AUD, CAD, EUR, etc. You need to integrate a lot of historical exchange rates, as these change through time. And so, the exchange rate equivalent is going to be very different, depending on the sale day.
I’m going to share my video tutorials on how you can solve this inside Power BI. It’s a bit complexed and advanced, and it’s important that you understand the DAX formula as well as the data model.
The first tutorial will show you how you can integrate the currency table into your data model, how the model is set up, and how to work out a formula to calculate sales with the adjusted currency values.
Watch the video on how I used the LOOKUPVALUE function inside an iterating function, SUMX, to get the correct calculation.
The LOOKUPVALUE function adjusts each sale item for the currency exchange rate on a certain day. The key is the currency adjustment and the process of unpivoting the columns so that the exchange rate was all in one row. Make sure that there is a relationship from the Dates column to the Exchange Rates column.
However, I found a slight issue with the particular formula though. The problem was that the exchange rate table doesn’t have any exchange rates on the weekend, but there are goods sold on the weekends.
With this formula, the amounts sold on weekends were being divided by a blank number. Consequently, we get this “infinity” result, which was because there was no result to showcase (blank number).
So, I came up with a more advanced DAX formula to solve this. It will carry over Friday rates to Saturday and Sunday. Check out the video below and see how I formulated the solution using a variable (VAR) and the LASTNONBLANK function, along with other DAX functions.
The LOOKUPVALUE formula is not actually bad. The only problem with it is that there was no currency exchange rate on the weekends. In this updated version, I show a solution that sets a number on the weekend, which is by using the Friday number and dragging it over to the weekend because currencies don’t trade on a weekend.
This was not an easy formula to come up with, and I hope you can get your head around how this works.
Now, since we’re talking about currency rates here, I’ll share this tutorial with you on how to download exchange rates into your Power BI model.
In this tutorial, I demonstrate how you can download the latest exchange rates in your Power BI reports by querying the web.
From its rawest form, I did some simple transformations and updates until it was ready to be integrated into the data model. The downloaded exchange rates automatically update as they’re updated on the website where you got them.
Key Take Away
Having to integrate currency data or exchange rates into a financial analysis is very common in any business dealing with sales from different regions of the world. In this post, I share with you a unique approach in solving this inside Power BI.
If you could learn and understand the DAX combination and data modeling techniques I use in the tutorials well, calculating your sales and revenue correctly would be fast and easy.
I hope you can implement this into your own financial analysis easily.
All the best!
Sam
***** Related Links *****
Creating A Currency Conversion Table In Power BI
Format Currencies In Power BI Using DAX
How To Deal With Products That Have Changing Prices Overtime In Power BI
***** Related Course Modules*****
Advanced Analytics in Power BI
***** Related Support Forum Posts *****
Multi-currencies With Correct Currency Label
Calculate Currency Exchange Impact Between 2 Years
For more exchange rates support queries to review see here….
Be the first to comment