Please help me with my problem that is simply made in Excel but not in PowerBI. At least, I am struggling to make this work in PowerBI.

Case description:

In the excel file, there is a table. In column “D” there are two formulas.

The formula in cell “D2” contains value 100.

The formula in cells greater that “D2” contains formula = D2*(1+E3).

I want to replicate the table`s calculation in PowerBI but I have been struggling to make it work almost two days. Please help, see attached the excel file with an example of how should the PowerBI measure work.

Here is the DAX Formula to get the previous row
DAX Formula =
VAR _SelectedGrowth =
SELECTEDVALUE ( Sheet1[Growth %] )
VAR _CurrentRow =
SELECTEDVALUE ( Sheet1[Index] )
VAR _PreviousRow =
CALCULATE (
MAX ( Sheet1[Formula] ),
FILTER ( ALL ( Sheet1 ), Sheet1[Index] < _CurrentRow )
)
VAR Result = _PreviousRow * ( 1 + _SelectedGrowth )
RETURN
Result

Hi @Roboboboberts, did the response provided by @MudassirAli help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

Hi @Roboboboberts, we’ve noticed that no response has been received from you since the 16th of December. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

Is there a way to to do this without the column “Formula” ?

In the perfect situation, I only have column “#” and column “Growth %”. If the index column value equals 1 then put 100 in that cell. If index column value is greater than one then replicate the excel formula exactly.

@Roboboboberts The result you are looking for is not achievable with DAX. However, it might be obtained with PQ and our M code expert @Melissa can say mo re about this.

Thanks for the bat-signal. The obvious downside to creating a recursive pattern in M is that it’s not dynamic on the Report side. Although I can’t quite make out if that will be a problem in this case - I’ve encountered a similar problem not that long ago and found another way to solve it using DAX.
.

To the best of my knowledge you cannot recursively self-reference a column in DAX however recursive formulas may have a pattern that can be derived. In this case a pattern based on PRODUCTX will work.

Solution Melissa =
VAR Base = MINX( ALL(Data[Formula]), Data[Formula] )
VAR vTable =
ADDCOLUMNS( Data,
"@Value", PRODUCTX(
FILTER( ALL(Data),
Data[Index] <= EARLIER( Data[Index] )
), 1+ Data[Growth %]
)* Base
)
RETURN
SUMX( vTable, [@Value] )

@Melissa , is there a way to do that think without having the Excel column “Formula” ? If I have to keep the column “Formula” in the excel table, the point of the PowerBi formulas loses the point.