Calculate with previous rows and Index in Power Query M

[Wesley0107]

Hi everyone, 

Please see the attached examples. 

As you can see, the calculation is always done using the previous value from the Change column. I used Index - 1 to do so and that works for me.
 

15 - 5 = 10
10 - 20 = 0
etc.

There is a lower bound in force. Values in the Balance column cannot fall below 0. However, it is necessary to keep adding from 0 if the value in the Change column is positive again. There has to be a reset.

However, this does not work for me. Power BI keeps on calculating causing different values to occur. This can be seen in the second image. So this is not how it is supposed to work.

Does anyone has an idea to solve this problem? Or does anyone has a best practice? Please note that I am not that advanced in Power BI. Please keep it simple :). 

Thank you very much in advance. 

[v-pnaroju-msft]

Hi @Wesley0107,

We are pleased to hear that your issue has been resolved. Kindly mark the response that resolved your issue as the accepted solution. This will help other community members facing similar challenges to find solutions more efficiently.

Please continue using Fabric Community for any help regarding your queries.

Thank you.

[Wesley0107 · Accepted solution]

Hi everyone,

Thank you very much for taking the time to look at my problem. I have used some of your tips and finally I managed to solve the problem with the code below:

// Specify the lower bound
Limit = 0,

// Obtain current index and ID
CurrentIndex = [Index1],
CurrentID = [ID],

// Obtain values from the Change Column
ChangeColumn = [Change],

// Filter the rows for the same person with a lower index
PreviousRows = Table.SelectRows(#"xxxxx", each [ID] = CurrentID and [Index1] < CurrentIndex),

// Calculate the cumulative sum of previous rows change, starting from 0
CumulativeSum = List.Accumulate(PreviousRows[Change], 0, (state, current) =>
let
NewSum = state + current
in
if NewSum < Limiet then Limiet else NewSum
),

// Calculate the total value including the current monthly change balance
AdjustedTotal = CumulativeSum + Change,

// Check the lower bound
FinalBalance = if AdjustedTotal < Limit then Limit else AdjustedTotal
in
FinalBalance),

As you can see, I first create some variables that I use later in the formula.
Furthermore, I tried to use the ‘Table.SelectRows’ function. In it, you can see that I build in a condition to ensure that the same ID is grabbed each time and then the previous row is looked at using ‘< CurrentIndex’.

Next, I am going to calculate the cumulative sum, starting from 0. Finally, I check the result against the limit.

This eventually worked for me. Thanks again for taking the time to look at my problem. If there are any questions, don't hesitate!

Please note that I have anonymised all the data so it may look a bit strange.

Regards,

Wesley

[v-pnaroju-msft]

Hi @Wesley0107,

We would like to inquire if the solution offered by @ronrsnfld and @Omid_Motamedise has resolved your issue. If you have discovered an alternative approach, we encourage you to share it with the community to assist others facing similar challenges.
Should you find the response helpful, please mark it as the accepted solution and add kudos. This recognition benefits other members seeking solutions to related queries.

Thank you.

[Omid_Motamedise]

List.Accumulate is an amazing functions for applying loop in power query, use the below formula to solve your problem

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0VYrViVYyAjJ1IUxjENPIAMw2QWKbAtkWYJYZSB9E0BzINFaKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Change = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Change", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Accumulate(List.FirstN(#"Changed Type"[Change],[Index]),0,(a,b)=>List.Max({a+b,0})))
in
    #"Added Custom"

for more information see my videos
https://youtu.be/G8PRbWuDcmQ?si=czPpI0KA740FlW5I

[ronrsnfld]

I would use a different technique that does not rely on an Index column, but rather uses the List.Generate function by which you can keep track of the last value.

Paste the code below into the Power Query Advanced Editor, then examine it and explore the applied steps

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0VYrViVYyAjJ1IUxjENPIAMw2QWKbAtkWYJYZSB9E0BzINFaKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Change = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type},{"Change", Int64.Type}}),
    
    //create list of running balance, respecting the lower bound of 0
    
    #"Running Balance" = List.Generate(
        ()=>[rb=#"Changed Type"[Change]{0}, idx=0],
            each [idx] < Table.RowCount(#"Changed Type"),
            each [rb = if [rb] + #"Changed Type"[Change]{[idx]+1}<0 then 0 else [rb] + #"Changed Type"[Change]{[idx]+1} , idx = [idx]+1],
            each [rb]),
    
    #"Add Balance Column" = Table.FromColumns(
        Table.ToColumns(#"Changed Type")
        & {#"Running Balance"},
        type table[Index=Int64.Type, Change=Int64.Type, Balance=Int64.Type])
in
    #"Add Balance Column"

Note that in the third argument of the List.Generate function, you can change the zero (0), to whatever Lower Bound you choose to use.