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

calerof's avatar
calerof
Impactful Individual
7 years ago

Finally with the help of EnterpriseDNA's video I could have the sales data converted from MXP to USD with the following code:

Sales in USD = SUMX( NetSales, 
                                   DIVIDE(
                                              NetSales[LineTotal],
                                              LOOKUPVALUE( ORTT[Rate], 
                                                ORTT[RateDate], VentasNetas[OINV.DocDate],
                                                ORTT[Currency], "USD" )
                                   )
                           )

where:

NetSales in the sales fact table,

ORTT includes the MXP/USD exchange rate by date.

 

I post this if it could be of any help.

 

Regards,

 

Fernando