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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
ArashZ
Helper I
Helper I

Live currency exchange rate

Hi Everyone,

 

I have a table like this:

ArashZ_1-1643919145030.png

 

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?

1 ACCEPTED SOLUTION
v-cazheng-msft
Community Support
Community Support

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.

vcazhengmsft_0-1644392223780.png

 

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.

vcazhengmsft_1-1644392223782.png

 

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!

View solution in original post

2 REPLIES 2
v-cazheng-msft
Community Support
Community Support

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.

vcazhengmsft_0-1644392223780.png

 

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.

vcazhengmsft_1-1644392223782.png

 

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
Super User
Super User

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



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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