cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

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?

1 ACCEPTED SOLUTION
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

4 REPLIES 4
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

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.

Frequent Visitor

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

Super User

Your data model is missing the Calendar table.

Announcements

Power BI Monthly Update - November 2023

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

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors