Converting amounts following filtred exchange rates parameters

[Anonymous]

Hello,

Could you please help me improving performances with this query:

The Exchange_Rates table contains only exchange rates from every currency to euro.

Amount:=
SUMX (
SALES,
VAR ExRateToPivot = LOOKUPVALUE (
Exchange_Rates[EXCHANGE_RATE_Value],
Exchange_Rates[Date], IF(ISFILTERED('CONVERSION DATE')=FALSE(), SALES[CONV_PERIOD], SELECTEDVALUE('CONVERSION DATE'[DATE ID])),
Exchange_Rates[CURRENCY_OUT],'EUR',
Exchange_Rates[EXCHANGE_RATE_TYPE], IF(ISFILTERED('RATE TYPE')=FALSE(), SALES[RATE_TYPE_ID], SELECTEDVALUE('RATE TYPE'[RATE_TYPE_ID])),
Exchange_Rates[CURRENCY_IN],SALES[ORIG_CURRENCY]
)
VAR ExRateToRestitution= LOOKUPVALUE (
Exchange_Rates[EXCHANGE_RATE_Value],
Exchange_Rates[Date], IF(ISFILTERED('CONVERSION DATE')=FALSE(), SALES[CONV_PERIOD], SELECTEDVALUE('CONVERSION DATE'[DATE ID])),
Exchange_Rates[CURRENCY_OUT],'EUR',
Exchange_Rates[EXCHANGE_RATE_TYPE], IF(ISFILTERED('RATE TYPE')=FALSE(), SALES[RATE_TYPE_ID], SELECTEDVALUE('RATE TYPE'[RATE_TYPE_ID])),
Exchange_Rates[CURRENCY_IN], SELECTEDVALUE('RESTITUTION_CURRENCY'[RESTITUTION_CURRENCY_ID])
)
RETURN [AMOUNT_IN_ORIGINAL_CURRENCY]* ExRateToPivot * DIVIDE(1,ExRateToRestitution)
)

[v-kelly-msft]

Hi  @Anonymous ,

Based on the expression, I think there's no other better quries to improve the performance,but if you could provide some sample data with expected output,maybe I could try another logic to fulfill what you need.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Thank you very much @v-kelly-msft 

Please find below some samples and expected outputs :

SALES

MONTH CONV_PERIOD RATE_TYPE_ID ORIG_CURRENCY AMOUNT_EUR AMOUNT_IN_ORIGINAL_CURRENCY
202001 201912 AVERAGE RATE USD 2000 2200
202006 201906 CLOSING RATE EUR 1000 1000
202009 202006 AVERAGE RATE GBP 1400 1000
202010 201712 AVERAGE RATE CHF 3000 3500

Exchange_Rates

Date CURRENCY_IN CURRENCY_OUT EXCHANGE_RATE_TYPE EXCHANGE_RATE_Value
201912 USD EUR AVERAGE RATE 0.909
201912 USD EUR CLOSING RATE 0.91
201906 EUR EUR AVERAGE RATE 1
201906 EUR EUR CLOSING RATE 1
201912 GBP EUR AVERAGE RATE 1.4
201912 GBP EUR CLOSING RATE 1.39
202003 USD EUR CLOSING RATE 0.87
202003 GBP EUR CLOSING RATE 1.37
201712 CHF EUR AVERAGE RATE 0.857
202003 CHF EUR CLOSING RATE 0.95

If the user want the amount filtred only on MONTH=202001 and RESTITUTION_CURRENCY='EUR' so :

AMOUNT=2200*0.909*(1/1)=2000 euros

If the user want the amount filtred only on MONTH=202001 and RESTITUTION_CURRENCY='GBP' so:

AMOUNT=2200*0.909*(1/1.4)=1428 pounds

If the user want the amount filtred only on MONTH=202001 and RESTITUTION_CURRENCY='GBP' and CONVERSION DATE='202010' and RATE_TYPE='CLOSING RATE' so :

AMOUNT=3500*0.95*(1/1.37)=2427 pounds

Best regards.