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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Johnweet
Helper I
Helper I

Converting currency

I have a data table that contains a list of transactions.  Each transaction has a date and the cost in local currency.  There is also a column with the currency code.  I then have a look up table that brings data in from a seperate source.  This table has a date field, the currency code and the conversion rate on that date.

 

I want to be able to add a column in my data table that brings in the local currency to USD conversion rate on the date of the transaction so that i can standardize the costs.

 

Can this be done?

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

You should be able to use the LOOKUPVALUE() function.  To get more help, share the download link of your PBI file and show the expected result there.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

You should be able to use the LOOKUPVALUE() function.  To get more help, share the download link of your PBI file and show the expected result there.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

@Johnweet , You can create table like this in you fact table

 

maxx(filter(Curr, Curr[Curr_code] = fact[Curr_code] && Fact[Date] = curr[Curr_date]) , curr[Rate])

 

you can multiple rate by amount and get the other currency value

I can't get that to work.  My data table is called "All_ads". My Currency table is called "USDBase_Bloomberg".  This is the formula i have.

 

Conversion rate = maxx(filter(USDBase_Bloomberg,USDBase_Bloomberg[Currency] = All_ads[Currency_local] && All_ads[Date] =USDBase_Bloomberg[Date]) ,USDBase_Bloomberg[Foreign_to_USD])
 
Where the fields
"USDBase_Bloomberg[Currency]" is a text field that contains a currency code 
"All_ads[Currency_local]" is also a text field that contains the equivalent currency code
"All_ads[Date]" is a date field showing the date of the transaction
"USDBase_Bloomberg[Date]" is also a date field showing the date of the conversion factor
"USDBase_Bloomberg[Foreign_to_USD" is a number field returning the exchange rate
 
The formula does not appear to see the All Ads fields, they are grayed out.
 
I have created a relationship between the date fields in the two tables and the currency / local_currency fields
 
This is my error message.
 
A single value for column 'Currency_local' in table 'All_ads' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
 
One other complication.  i do have some USD values in my data table but no USD - USD cconversion in the currency table.  I need to add a conversion of 1 for USD.
 
Thanks
 

Hi @Johnweet ,

 

Are you creating a measure? Please try this.

Conversion rate: = 
MAXX (
    FILTER (
        USDBase_Bloomberg,
        USDBase_Bloomberg[Currency] =MAX( All_ads[Currency_local])
            && MAX(All_ads[Date]) = USDBase_Bloomberg[Date]
    ),
    USDBase_Bloomberg[Foreign_to_USD]
)

Best Regards,
Community Support Team_Gao

 

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 us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors