Dynamic Conversion rates in USD based on the "Order Booked date" or "Date"

[jeje09]

Hi Folks,

As you can see on the subject, I'm not so sure if this is feasible, but here's the context:

I have a report that involves invoices expressed in to different currencies. Now, the business want to see the figures in USD.

What i want to achieve is to convert those Non-USD invoices in to USD based on when the order is booked (so the conversion should be dynamic, could be the conversion rate is set to monthly, weekly, or annually).

Also, much better if you can provide a FREE (free API key) reliable website for real time conversion rates that can also get the historical rates from 2018 up until today, 2024.

I hope i made it clear and if it's feasible. Thank you experts! 🙂

[Omid_Motamedise]

You can connect your query to the XE.Com website and load the data from this website. but its dependent to the granulity of your data is it in data or even in hour or minute, you might need to use group rows of data from this website.

[jeje09]

Can i also get the historical rates with FREE API key?

[Poojara_D12]

Hi @jeje09 

To achieve dynamic currency conversion based on when the order is booked (with historical rates if needed), you can follow these steps:

Key Steps:

1. Dynamic Currency Conversion:

   • Data Setup: Have a table with invoices, including the currency and order booking date.
   • Currency Conversion Table: Create or use an external data source with historical exchange rates. This table will include columns like Date, CurrencyFrom, CurrencyTo, and Rate.
   • DAX Formula: Use DAX to dynamically convert the invoice amounts to USD by fetching the correct conversion rate based on the booking date.

   Example DAX for conversion:

ConvertedAmount = 
'Invoices'[Amount] * 
CALCULATE(
    MAX('CurrencyRates'[Rate]),
    'CurrencyRates'[CurrencyFrom] = 'Invoices'[Currency] && 
    'CurrencyRates'[CurrencyTo] = "USD" && 
    'CurrencyRates'[Date] <= 'Invoices'[OrderDate]
)

2. Reliable Free API for Currency Conversion:

• You can use the ExchangeRate-API (Free plan available), which provides real-time exchange rates and historical data from 2018 up until today.

  3. How to Implement in Power BI:

  • Get Exchange Rate Data: Use Power Query to fetch data from the API, including real-time and historical rates.
  • Set the date filter in Power Query to ensure that the correct rate for the invoice booking date is applied.
  • Use Dynamic Filtering in your model to pull the exchange rate that matches the invoice booking date for conversion to USD.

Summary:

• Dynamic Conversion: Use DAX with a currency rates table to convert non-USD invoices to USD based on the booking date.
• Free API: Use ExchangeRate-API for free access to real-time and historical exchange rates (up to 2018).

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[wdx223_Daniel]

1, create a ExchangeRateHistory table with columns such as Date, Rate, ....

2, add a calculated column in the order table

ExRate=MAXX(TOPN(1,FILTER(ExchangeRateHistory,'ExchangeRateHistory'[Date]<='order'[Date]),'ExchangeRateHistory'[Date]),'ExchangeRateHistory'[Rate])