Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Karimthedream
Frequent Visitor

currency conversions using daily rates - now PBI is super slooooooow

Hi,

 

im pretty new to PBI and i am struggling with conversion rates.

 

in summary:

  • we have orders that come into a conversions table, these orders can be in any currency (one column for the currency, one column for the amount)
  • we then have a commission amount associated with each order, this too can be in any currency (again one column for the currency, a second column for the amount)
  • the order currency and the commission currency can be different for the same order

 

i would like to be able to convert both the order currency and the commission currency to GBP based on the exchange rate of the order date

 

My solution so far:

i have brought in a currency exchange rate table for all currencies to GBP, which provides a rate for each day, an extract of the table is below

Karimthedream_0-1682356764871.png

i then created a custom column to work out the exchange rate based on the order currency and the date of the order:

 
Order Amount Exchange Rate =

VAR OrderCurrency = 'Conversions & CPC Tables & PublisherConversions (Partners DB)'[OrderAmount_CurrencyCode]
VAR OrderDate = 'Conversions & CPC Tables & PublisherConversions (Partners DB)'[CreatedAt - DATE ONLY]
VAR ExchangeRate =
    CALCULATE(
        MIN('Exchange Rates'[Rate]),
        FILTER('Exchange Rates', 'Exchange Rates'[Currency] = OrderCurrency
            && 'Exchange Rates'[Date] = OrderDate)
    )
RETURN ExchangeRate
 
and then a second column to do the division:
 
Order Amount GBP = 'Conversions & CPC Tables & PublisherConversions (Partners DB)'[OrderAmount_Amount]/'Conversions & CPC Tables & PublisherConversions (Partners DB)'[Order Amount Exchange Rate]

 

although this solution works, the whole report is incredibly slow (both in the desktop and cloud) compared to the solution i used previously, which was just using a conditional column to set a desired exchange rate based on the order currency, then dividing one column by the other. however i am after a solution that uses live exchange rates so i can use the data for financial reporting.

 

is there a more efficient way to do this? 

 

thanks for your help

1 ACCEPTED SOLUTION
Karimthedream
Frequent Visitor

Hi,

 

i managed to resolve the issue. It wasnt the currency conversions that was slowing down the report, it was the fact that i had a lot of different relationships between tables/databases. i managed to reduce the number of relationships by using the lookup function with dax to essentially add data from one table to another, that way when building visuals, all the data was coming from 1 table, rather than various tables via relationships.

 

Thanks

View solution in original post

4 REPLIES 4
Karimthedream
Frequent Visitor

Hi,

 

i managed to resolve the issue. It wasnt the currency conversions that was slowing down the report, it was the fact that i had a lot of different relationships between tables/databases. i managed to reduce the number of relationships by using the lookup function with dax to essentially add data from one table to another, that way when building visuals, all the data was coming from 1 table, rather than various tables via relationships.

 

Thanks

lbendlin
Super User
Super User

i have brought in a currency exchange rate table for all currencies to GBP, which provides a rate for each day,

that may be too much data. How many currencies are you tracking?

 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Hi, thanks for getting back to me.

 

Currently i am allowing for any currency worldwide, since thats the data we are currently receiving. the URL below will take you to a file with some dummy data, essentially each of the columns with an amount would need to be converted into GBP, based on the exchange rate of the day of that row. with each of the columns potentially being in a different currency. any help would be greatly appreciatated.

 

thanks 

Dummy Data file for PBI 

Your data model is missing the Calendar table.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.