Forum Discussion

sgannon1's avatar
sgannon1
Frequent Visitor
10 years ago
Solved

Convert currency

I need to convert the 'amount' column in the below table to EUR (currency is GBP at the moment), and then to merge the table with another that is already in EURO. 

So far I have not been able to do this without returning errors. 

I have a currency conversion table (also below) that shows todays exchange rate, however I am unsure how to use the table to write a formula that will convert the amount.? Any help would be appreciated. 

 

 

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    10 years ago

    This will work if you get rid of all of your non-GBP exchange rates. Do you need those? If you do, sure some more DAX magic can get you there.

     

    Column = LOOKUPVALUE(ExchangeRates[ExchangeRate],ExchangeRates[CurrencyCode],"GBP",ExchangeRates[StartTime],MAX(ExchangeRates[StartTime]))

    Oh wait, here is the additional DAX magic that will let you keep your other currency codes.

     

    Column = LOOKUPVALUE(ExchangeRates[ExchangeRate],ExchangeRates[CurrencyCode],"GBP",ExchangeRates[StartTime],CALCULATE(MAX(ExchangeRates[StartTime]),ExchangeRates[CurrencyCode]="GBP"))

9 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    10 years ago

    This will work if you get rid of all of your non-GBP exchange rates. Do you need those? If you do, sure some more DAX magic can get you there.

     

    Column = LOOKUPVALUE(ExchangeRates[ExchangeRate],ExchangeRates[CurrencyCode],"GBP",ExchangeRates[StartTime],MAX(ExchangeRates[StartTime]))

    Oh wait, here is the additional DAX magic that will let you keep your other currency codes.

     

    Column = LOOKUPVALUE(ExchangeRates[ExchangeRate],ExchangeRates[CurrencyCode],"GBP",ExchangeRates[StartTime],CALCULATE(MAX(ExchangeRates[StartTime]),ExchangeRates[CurrencyCode]="GBP"))
    • calerof's avatar
      calerof
      Impactful Individual
      7 years ago

      Hi Greg_Deckler, v-haibl-msft,

       

      I'm struggling with a solution that converts each invoce to another currency based on the invoice date. I just can not find a solution here in the Community for that approach.

       

      Could you please advise?

       

      Thanks,

       

      Fernando

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      I've a similar problem, in my case with nominal to constant currency....
      anyway, in the proposed solution there are using the latest available exchange rate (MAX(StartTime))... i'm using it and it works fine, but can I make an slicer or use another pickable menu to change the date of the exchange rate used? What if I wish to know the price in GBP of March 1 2017?

      Thanks

  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    10 years ago

    sgannon1

     

    The solution provided by Greg_Deckler should work. And you can also try with following column formula.

    Converted Amount_Column = 
VAR LatestDate =
    CALCULATE (
        MAX ( 'Currency Conversion'[Starting Date] ),
        'Currency Conversion'[Currency Code] = "GBP"
    )
RETURN
    (
        CALCULATE (
            VALUES ( 'Currency Conversion'[1.2 Exchange Rate Amount] ),
            FILTER (
                'Currency Conversion',
                'Currency Conversion'[Currency Code] = "GBP"
                    && 'Currency Conversion'[Starting Date] = LatestDate
            )
        )
            * Table1[1.2 Amount]
)

    Best Regards,
    Herbert