Latest Enterprise DNA Initiatives

Reference a formula from previous row

DAX Calculations
#1

Hi,

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:
image

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.

Replicate this.xlsx (9.4 KB)

#2

@Roboboboberts

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

1
11023×693 12.9 KB
Reference a Formula From Previous Row.pbix (24.2 KB) Replicate this.xlsx (9.4 KB)

Thanks.

3 Likes
#3

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!

1 Like
#4

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!

1 Like
#5

Hi @MudassirAli,

Thank you!

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.

#6

Hi,
Can anyone help? I still have not been able to make this work.

Thank you!

#7

@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.

#8

Hi @MudassirAli,

Thanks for the bad-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.
.

Hi @Roboboboberts,

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] )

.

With this result.

image

I hope this is helpful.

3 Likes
#9

Hi @Melissa,

Thank you! I just cannot make it work. I keep getting a constant value for every row. Can you please provide mwith the PowerBI file ?

image

Thank you!

#10

@Roboboboberts,

Yep no worries, here you go, it also includes a PQ Solution as alternative.
eDNA - Reference a Formula From Previous Row.pbix (27.7 KB)

1 Like
#11

@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.

#12

Hi @Roboboboberts,

You can see that the Formula column is only referenced once for the Base variable.

VAR Base = 100 //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] )

Changing this into a static value or … (whatever suits your requirement) does the trick.

The same is true for the PQ solution of course.

#"Added Custom" = Table.AddColumn(ReplaceNull, "PQ Calc", each 
    let
        myValue = 100 /*ReplaceNull{0}[Formula]*/ * 1+ ReplaceNull{0}[#"Growth %"],
        indexList = List.Buffer (ReplaceNull[Index]),
        percList = List.Buffer (ReplaceNull[#"Growth %"]),
        myList = List.Generate(
            ()=> [myValue = myValue, i = 0, y = List.Max(indexList)-1 ],
            each [i] <= [y],
            each [myValue = [myValue] * ( 1+ percList{[i]+1} ), i = [i]+1, y = [y] ]
        ),
        CreateList = Table.FromRecords(myList){[Index]-1}[myValue]
    in
    CreateList, type number
)
3 Likes
#13

Hi, made it work, thank you!

For me, the DAX seems straight forward but the Power Query rather advanced. Is there lecture for so advanced level M code on this website?

Thank yoU!

#14

Hi @Roboboboberts,

eDNA is working hard to extend the courses within the portal, additional Power Query modules will hopefully become available some time next year…

Futher more you can give feedback here

2 Likes