Dynamic Currency Converter within Power BI

Within this post we will cover how to create a Dynamic Currency Converter using Power BI.

Assumptions

Listed below are the assumptions applied to the example:

  1. Access to a Currency Exchange rate table (with no missing dates)
  2. Currency will be converted on the date the transaction took place.
  3. The converter will take multiple currency’s and display them as one.

The first assumption, is necessary to create the data model, an exchange rate table will be required to have every day of the year including weekends present. If the table is missing dates, there are ways to fill them in using approximation however this will not be covered in this post.

Next, we assume that all transactions are going to be converted on the day the sale took place. The data model will use the transaction date, to look up and find the conversion rate for the desired day.

Finally, within this example we will only cover, converting multiple currencies and displaying them as one. There are other business requirements, which may require converting one transaction currency into multiple report currencies or multiple currencies into multiple report currencies. However, these will not be covered today.

Data Model

Below is the data model, used to complete our task:

Currency Converter Data Model

The Fact tables (TransactionData and CurrencyExchange), are related using entity link tables.

The TransactionCurrency,ExchangeDate and CurrencySelection table’s are all derived from the CurrencyExchange using the formulas below:

These formulas result in tables consisting of distinct currencies/dates from tjhe CurrencyExchange Table.

The relationships displayed within the model are set as follows:

Creating the converter

First we need to create the measure that will be handling the converting (see image below).

      Currency Converter Measure

Variables are used to easily present the code and make it more manageable when debugging. If no slicer is present the currency is defaulted to “GBP”.

This measure combined with the below one, will apply the currency conversion on a row by row bases within the transaction table.

Next we need to create a slicer, using our CurrencySelection Table and the “Change currency” field.

Along with a visual to display our results (for the purpose of this demonstration, a table will be used).

GBP:

EUR:

USD:

Conclusion

It’s as simple as that, a dynamic currency converter created within Power BI.  This solution allows the report to have any number of visuals to display converted currencies.

The basis of the converter solution can be expanded upon by adding a Company Hierarchy table to filter currencies based on subsidiary locations, but we can cover that next time.

Would you like to know more?

Would you like to know more on how your organisation can take advantage of Power BI. Contact us on the link below. https://www.risual.com/contact-us/

About the author