cancel
Showing results for
Did you mean:
Helper III

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

## Re: Equivalent of array forumla in Excel - PRODUCT - Calculating linked returns

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Microsoft

## Re: Equivalent of array forumla in Excel - PRODUCT - Calculating linked returns

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper III

## Re: Equivalent of array forumla in Excel - PRODUCT - Calculating linked returns

This worked!  Thanks Lydia!

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

#### ‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors