Equivalent of array forumla in Excel - PRODUCT - Calculating linked returns

[dancarr22]

Hello,

We use an array formula to calculate returns in Excel.  Please see details below.

How cna this be replicated in Power BI?

Thanks,

Dan

[Anonymous · Accepted solution]

@dancarr22,

Firstly, create a column in your table.

return2 = 1+Table1[Returns(%)]/100

secondly, create a measure using DAX below in your table.

Measure = 

VAR DATECOL= MAX(Table1[Date])
RETURN (CALCULATE(
        PRODUCT(
           Table1[return2]
            ),
            FILTER(
                Table1,
                Table1[Date]<=DATECOL )
               )-1)*100

Regards,
Lydia

[dancarr22]

This worked!  Thanks Lydia!