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 ...
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"))
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

Greg_Deckler's avatar
Greg_Deckler
Community Champion
7 years ago

calerof - Can you provide example/sample source data and expected output?

  • calerof's avatar
    calerof
    Impactful Individual
    7 years ago

    Hello Greg_Deckler,

     

    Sorry for not answering before, my client started requesting things afar from the agreement. 

     

    The data are as follows:

     

    Fact Table with sales by invoice line:

    https://www.dropbox.com/s/hz5nc6kqda4wbv9/Daily%20Revenue%20by%20invoice%20item%20Original.xlsx?dl=0

     

    Exchange rate table:

     

    DateUSD
    01-ene-1919.6512
    02-ene-1919.6512
    03-ene-1919.5878
    04-ene-1919.6073
    05-ene-1919.6073
    06-ene-1919.6073
    07-ene-1919.4902
    08-ene-1919.3479
    09-ene-1919.3479
    10-ene-1919.2456
    11-ene-1919.1236
    12-ene-1919.1236
    13-ene-1919.1236
    14-ene-1919.1236
    15-ene-1919.0064

     

    In excel I would do it just easily with =vlookup convert all sales in MXP to USD based on the invoice date and bring the sales already in USD to the same column to have a total in USD, e.g.

    https://www.dropbox.com/s/jvwex5u8o0l7mo9/Daily%20Revenue%20by%20invoice%20item.xlsx?dl=0

     

    In this way the last column would show Sales in USD converted at the invoice date, not at month end.

     

    When I use LOOUPVALUE my problem is when assigning the current date in SEARCH VALUE doesn't recognize the row context and get the error: "A table of multiple values was supplied where a single value was expected".

     

    LOOKUPVALUE (
        EXCHANGERATES[RATE],
        EXCHANGERATES[DATE], DATE ( 2019, 1, 12 ),
        EXCHANGERATES[CURRENCY], "MXP"
    )
     
    Could you please advice?
     
    Thanks a lot,
     
    Fernando
     
    • calerof's avatar
      calerof
      Impactful Individual
      7 years ago

      Hi Greg_Deckler,

       

      So as you can see, my intent here is to be able to convert each individual invoice based on its date's exchange rate, not one final convertion at month end with only one exchange rate.

       

      Could you advise on a way to do it?

       

      Thank you for your help.

       

      Fernando

      • calerof's avatar
        calerof
        Impactful Individual
        7 years ago

        Hello Greg_Deckler,

         

        Sorry to insist. I'd just only like to know if there is a way I can solve my problem of currency conversion in Power BI.

         

        Thanks and regards,

         

        Fernando