Forum Discussion
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 tha...
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.
Anonymous , 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