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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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