Live currency exchange rate

[ArashZ]

Hi Everyone,

I have a table like this:

I set the conversion rate at 1.3 and used the if formula to create a "total payment after conversion" column. However, is there a way that I can use the live conversion rate as it fluctuates every day?

In that case, what is the formula?

[v-cazheng-msft · Accepted solution]

Hi @ArashZ 

Just as tomfox said, if you would like to get the fluctuating conversion rate of current day, you need to detch it from a website. In my sample, I get this data from this website: https://www.xe.com/currencyconverter/convert/?Amount=1&From=USD&To=CAD.

After connecting to the data source with Web connector, you need transform the data retrieved to extract the specific conversion rate between USD and CAD in Power Query. Just like this screenshot displaying. When everything is ready, close and apply the transformation in Power Query, and this conversion rate will be imported into your model.

Then, you need to create a Calculated column to do the conversion for you.

TotalPaymentAfterConversionUSD =

VAR CurrentConversionRate =

    SELECTEDVALUE ( ConversionRate[CAD] )

RETURN

    IF (

        'Table'[Currency] = "CAD",

        'Table'[Total Payment] * CurrentConversionRate,

        'Table'[Total Payment]

    )

And the result will look like this.

To update the conversion rate and the result of conversion, you need to arrange daily data refresh for your dataset. For how to set scheduled refresh, you may refer to Data refresh in Power BI - Power BI | Microsoft Docs.

Also, attached the sample pbix file as reference.

Best Regards,

Community Support Team _ Caiyun

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

[tackytechtom]

Hi @ArashZ 

Currently Power BI cannot get the exchange rate of a date by using a dax formular. Here are some blogposts that might help anyway:

how to get the latest exchange rates straight into power bi. - tacky tech.

multiple-currency-logic-in-power-bi-lookupvalue-example 

Handling Multiple Currencies in Power BI w/DAX 

hope this help!

Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom