Convert Excel Formula using INDEX

[Syndicate_Admin]

I'm a Power BI newbie.

I have an excel sheet (ODBC) that I want to convert to Power BI, I already created the table but I have a formula in Excel that I don't know how to convert to Power BI, I use the INDEX and MATCH functions, but when I try to do the same in my table as a new column I don't get the INDEX function.

The formula I have looks for the first occurrence of month and year in DateColumn that has CURRENCY = PES and then retrieves the value of ExchangeTypeColumn to multiply it by AMOUNT, to get the BALANCE column

Here's the Excel formula:

IF(CURRENCY= "PES", AMOUNT, AMOUNT*INDEX(ColumnRateExchange,MATCH(1,(EXTRACT(DATE,5,2)=EXTRACT(ColumnDates,5,2))*(LEFT(DATE,4)=LEFT(ColumnDates,4))*(ColumnCurrency="PES"),0)))

And example data:

A.	Name	Date	Coin	Amount	T.C.	Balance
3001	Prov1	20231231	USD	11050.4	1	190373.15
3001	Prov1	20231231	USD	31162.61	1	536860.59
3005	Prov2	20231206	PES	9048	17.22771595	9048
3005	Prov2	20231231	PES	44118	16.89351846	44118

How do I create the formula in a calculated column in Power Bi? I'm using Direct Query to get the data.

Thank you

[TheoC]

Hi @Syndicate_Admin 

To do it, you will need to take a few steps:

1. Calculated Column for Year:

Year = YEAR ( [Date] )

2. Calculated Column for Month:

Month = MONTH ( [Date] )

3. Calculated Column for FX Rate:

ExchangeRate =

VAR TargetYear = [Year]
VAR TargetMonth = [Month]

RETURN

CALCULATE (
     FIRSTNONBLANK ( Table[ExchangeTypeColumn] , 0 ) ,
          FILTER ( Table, YEAR ( Table[Date] ) = TargetYear &&
          MONTH ( Table[Date] ) = TargetMonth &&
          Table[CURRENCY] = "PES" ) , REMOVEFILTERS ( 'Table' )

)

4. Calculated Column for the Balance:

Balance = IF ( [CURRENCY] = "PES" , [AMOUNT] , [AMOUNT] * [ExchangeRate] )

Please adjust / adapt and hopefully it helps or at least guides you through the process.

All the best.

Theo

[Syndicate_Admin]

Thanks for the reply, I assume that what was translated as CALCULATE is equivalent to the "CALCULATE" function, but I don't know what function can be RETURN.

How to put the function in English without having to translate it?