none
Need help converting index match formula into powerbi calculated field RRS feed

  • 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 ? 

    Than you in advance..

    Wednesday, April 24, 2019 8:13 AM