Converting currency

[Johnweet]

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?

[Ashish_Mathur · Accepted solution]

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.

[amitchandak]

@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

[Johnweet]

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

[v-cgao-msft]

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