How to use INDEX DAX function in a calculated column?

[martipe1]

I'm a newbie in PowerBi.

I have an excel report trying to convert to Power Bi, my last column in Excel file is a calculated using Index-match functions, but I can't figure it out how to convert it to a calculated column in Power BI.

My excel formula is:

=IF([@CURRENCY] = "PES",[@[AMOUNT]], [@[AMOUNT]]*INDEX(L:L,MATCH(1,(MID([@DATE],5,2)=MID(E:E,5,2))*(LEFT([@DATE],4)=LEFT(E:E,4))*(G:G="PES"),0)))

Thanks in advance for your help

[martipe1]

Anybody that can help me out?

[martipe1]

Thank you very much for your answer.

What I see your answer does is multiplies the amount by the sum of all amounts where the currency equals "PES"

What I'm attemping to do is to multiply the amount by the exchange rate (columns L:L) where the currency matches "PES" (G:G) and the month and year of that exchange rate matches the date of the amount

Any suggestion?

[Fowmy]

@martipe1 

Sharing a dummy Power BI file representing your scenario would be beneficial. You can save the Power BI file on Google Drive or any other cloud storage platform and provide the link here. Kindly ensure that permission is granted to open the file.

[martipe1]

Thank you for your answer.

I uploaded a dummy Excel and Power BI files. The excel file has the formula I want to reproduce in Power Bi

Dummy files

Thanks!!

[Fowmy]

@martipe1 
Please try:

NewColumn =
IF (
    'YourTable'[CURRENCY] = "PES",
    'YourTable'[AMOUNT],
    'YourTable'[AMOUNT]
        * CALCULATE (
            SUM ( 'YourTable'[AMOUNT] ),
            FILTER (
                'YourTable',
                MID ( 'YourTable'[DATE], 5, 2 ) = MID ( 'YourTable'[DATE], 5, 2 )
                    && LEFT ( 'YourTable'[DATE], 4 ) = LEFT ( 'YourTable'[DATE], 4 )
                    && 'YourTable'[CURRENCY] = "PES"
            )
        )
)