Power BI Exchange Rates: An Update To Multiple Currencies Management

There are many instances with financial analysis wherein we need to integrate exchange rates, and we can do this easily in Power BI. You may watch the full video of this tutorial at the bottom of this blog.

I have actually shown an example of how to do this using the LOOKUPVALUE function in Power BI.

However, I found one slight issue with the particular function that I went through in the initial video that I didn’t realize at the time.

The problem was that the exchange rate table did not have any exchange rates listed on the weekend, but we did sell goods on the weekend.

What was happening was that the amount sold was being divided by a blank number. As a result, we were getting this infinity result, which was because there was basically no result to showcase.

error 222

So what we needed to do was something slightly more advanced in our DAX formula.

We have to actually normalize the exchange rate. Whatever the result was in the table for the Friday, we need to copy that to Saturday and Sunday.

We must re-enact that inside of Power BI with our formula and in this tutorial, I run through how you do that.

But, before we get into the updated formula, we will first review how we handle exchange rates and multiple currencies in Power BI using the LOOKUPVALUE function.

Handling Multiple Currencies Using LOOKUPVALUE

We put the LOOKUPVALUE function inside an iterating function, which is SUMX. 

Here, we want to sum up the revenue, but we adjust every single row in the Sales Data table. This way, we have the associated exchange rates for each sale.

power bi exchange rates

The LOOKUPVALUE  function does is literally “look up” the value for the particular exchange rate on specific dates and other relevant information for your SUMX calculation.

In this formula, every single sale will be adjusted for the currency rate on that particular date. 

Flaw In The LOOKUPVALUE Formula

The formula with LOOKUPVALUE is not actually bad. The only problem with it is that there was no currency exchange rate on the weekends.

On the first glance, we would not notice it when we look at the historic data. It would appear that every single day has a currency exchange rate.

However, when we look closer, the weekends are actually missing. This means that the result of the calculation is not accurate.

error 333

The solution is to set a number on the weekend. And to do this, we use the Friday number and drag it over Saturday and Sunday because currencies don’t trade on a weekend.

Handling Multiple Currencies Using LASTNONBLANK

To achieve the solution to the missing weekend number, we need to create a new formula that extends the Friday number over Saturday and Sunday.

Here’s the formula:

power bi exchange rates

This formula, which I called Exchange Rate Normalized, uses a variable (VAR) with FILTER (RevisedDates) that is incorporated into CALCULATE with the LASTNONBLANK function.

The LASTNONBLANK looks into the last exchange rate in a RevisedDates table, which we used in combination with the FILTER function.

The RevisedDates table looks back at three days, and then grabs the last exchange rate that exists on those days.

We can then integrate this Exchange Rate Normalized formula into our Total Sales Ccy Adj. formula, simplifying it.

power bi exchange rates

This is how your data looks like with the updated Total Sales Ccy Adj. formula with the Exchange Rate Normalized that extends Friday over the weekend:

new data

***** Related Links *****
Multiple Currency Logic In Power BI – LOOKUPVALUE Example
How To Download Latest Exchange Rates Into Your Power BI Model
How To Deal With Products That Have Changing Prices Overtime In Power BI

Conclusion

This blog discusses the updated formula for handling multiple currencies in Power BI.

Instead of using the LOOKUPVALUE, we use the LASTNONBLANK function in a separate formula, Exchange Rate Normalized, which is then incorporated to our calculation for Total Sales Ccy Adj.

Coming up with this formula was not easy, but it’s a very powerful tool for your calculations.

There’s a little bit to it from a DAX perspective. However, if you can get your head around how this works, especially the LASTNONBLANK function, then there are many implications of similar techniques across many analytical scenarios you might find yourself having to analyze on a day-to-day basis.

Cheers,

Sam

Enterprise DNA Power BI On-Demand

The Latest

If you’ve been working with Python for data analysis or machine learning, you’ve likely come across NumPy arrays. They’re a powerful tool for handling numerical data, but sometimes, the data…

How to normalize a numpy array a quick guide.

Multiplying lists in Python is a common operation when performing mathematical computations or solving problems in data manipulation. There are multiple ways to achieve this task, depending on your requirements…

How to Multiply Lists in Python: A Simplified Guide

If you’ve been wrestling with Python lists and wondering how you can save them as a neat CSV file, you’re in the right place. One of the most common tasks…

How to Write a List to CSV in Python

Do you need to write an essay on the fall of the roman empire with accurate citations but have no time to find them? Are you tired of painfully having…

What is Caktus AI: A comprehensive overview

Pandas is a widely used Python library for data manipulation and analysis. One essential functionality that pandas can provide you is the ability to modify the structure of a dataset….

Pandas Drop Index: Efficiently Remove DataFrame Rows or Columns

Working with strings is a common task in Python. You often need to figure out whether a string contains another string. The simplest methods to check if a Python string…

7 Ways to Check if a Python String Contains Another

Ever found yourself knee-deep in Python files, struggling to import a module from a parent directory? If so, you’re in good company. Python’s import system can sometimes feel like a…

Python Import from Parent Directory: A Quick Guide

Data is the backbone of businesses these days, and having proof that you know how to handle and make the most out of data is a big deal in the…

Microsoft DP-500

In programming, you may encounter situations where a variable does not have a value. These instances are commonly represented by a special value known as null. Many programming languages, such…

Null Python: 7 Use Cases With Code Examples

Truncating a floating-point number in Python is a common operation that many programmers encounter in various applications. The process involves removing the decimal place of a float, leaving only the…

Python Truncate Flow: A Streamlined Approach for Efficient Code Execution

OpenAI’s ChatGPT tool has taken the world by storm and has been at the forefront of revolutionizing the way we generate content, do research, and even create code. And just…

Is ChatGPT worth it, let's work it out.

As you continue your journey as a Python programmer, you’ll want to write code that is more efficient, readable, and easy to maintain. The Python programming language comes with a…

Python Inline If: Simplify Your Conditional Expressions

Load More