Need help converting index match formula into powerbi calculated field

• Question

• Hi Power Users,

I have a excel formula that does what i need , but now to translate that to work in Power Bi has been a nightmare.

I tried to google for answers but no luck. Okay so hopefully my table and explanation will make sense. I have the fields in Table DEAL (AMOUNT_IN_CCY, CCY, AMOUNT_EUR , DATE) . There is also another matrix Table called FX_RATES where I store for each day (historically from year 2000 till today) the currencies against EUR. I have  the following fields in there; DATE, EUR, USD, JPY, GBP,... (the entire set of available currencies).

 Table Deal DATE AMOUNT_IN_CCY CCY AMOUNT_EUR 31.12.2018 100 GBP =100/0,952 31.12.2018 120 EUR =120/1 31.12.2018 2250,5 EUR ... 31.12.2018 320,1 USD 31.12.2018 30 JPY 31.12.2018 11 USD 31.12.2018 25554 EUR … … …

 Table FX_Rates DATE EUR USD GBP JPY … … 31.12.2018 1 1,245 0,952 1,85 30.12.2018 1 1,223 0,963 1,84 29.12.2018 1 1,254 0,941 1,85 28.12.2018 1 1,201 0,941 1,88 27.12.2018 1 1,212 0,935 1,86 26.12.2018 1 1,243 0,93 1,88 25.12.2018 1 1,222 0,9245 1,89 … … … … …

Normally  cope with this situation in excel with index match formula that searches dynamically the ccy and looks up the corresponding value for that specific date. :

like AMOUNT_EUR=AMOUNT_IN_CCY/ INDEX(FX_Rates!\$A\$1:\$G\$10;MATCH(Deal!A2;FX_Rates!\$A\$1:\$A\$10;0);MATCH(Deal!C2;FX_Rates!\$A\$1:\$G\$1;0))

Now my question is how to create a calculated field that calculates AMOUNT_EUR ?

Wednesday, April 24, 2019 8:13 AM