Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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?

Need Help
Tutorial Requests
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    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.

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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
       
      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Anonymous ,

         

        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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    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.