Accumulative, conditional accumulative in Power Query

[v-henryk-mstf]

Scenario:
In Power BI, we sometimes meet cumulative problem. For basic direct accumulation we could use Dax language or M language. But for conditional accumulation and group conditional accumulation, we tend to use M language. The following will be divided into three cases to gradually explore the cumulative summation.

1. Direct accumulation
2. Conditional accumulation
3. Group Conditional Accumulation

Original table:

Date	Part	Net Quantity
30/06/2021	A	300
31/07/2021	A	-150
31/08/2021	A	-200
30/11/2021	A	-200
31/12/2021	A	50
31/01/2022	A	-200
30/06/2022	A	100
30/06/2021	B	100
31/07/2021	B	20
31/08/2021	B	20
30/09/2021	B	-30
31/10/2021	B	-35
30/11/2021	B	25

Expected result:
Part 1: Direct accumulation

Part 2: Conditional accumulation

Part 3: Group conditional accumulation

Part 1: Direct accumulation
If accumulate [Net Quantity] in a table, we can use list.accumulate(), specific operations are as follows:
1. Add index column

2. Add custom column

=List.Accumulate(List.FirstN( #"Added Index"[Net Quantity],[Index]),0,(x, y) => x + y)

List.Accumulate() function requires three parameters,
The first parameter is a List, here is the List.FirstN() function which is to extract the first N items of the list;
The second parameter is the initial value, which is set to 0 here;
The third parameter is the accumulator function. There must be two values in the accumulator function. The first is the current state value of the accumulator function (represented by x), and the current item as a list parameter (represented by y); Before the accumulator function in the List.Accumulate function runs for the first time, the value of the state parameter is specified by the value in the initial value (0).

Combined with this case, the operation process of List.Accumulate is as follows:
In the first step, the x= 0 (the initial value of the second parameter), and the y = 300 (the first item in the List parameter). Then execute the calculation logic defined by the accumulator function: x + y. therefore:
The result of the first calculation: x= 0, y= 300, after adding x + y, the calculation result 300 is assigned to x to replace the original value.
The result of the second calculation: x= 300, y= -150, after adding x + y, the calculation result 150 is assigned to x to replace the original value.
The result of the third calculation: x= 150, current=-200, after adding x + y, the calculation result -50 is assigned to x to replace the original value.
...and so on

3. Outputs

Part 2: Conditional accumulation
Add one more condition based on Part 1 (if the sum is less than 0, the sum starts again from the next number)
1. Add index column

2. Add custom column

= List.Accumulate(
List.FirstN( #"Added Index"[Net Quantity],[Index]),0,(x, y) => if x<0 then y else x + y)

Just add condition here, if x is less than 0, it will be re-totaled.

3. Outputs

Part 3: Group conditional accumulation
The above part is accumulated without groups, so what if it is accumulated with groups?
1. Add index column by group

= Table.Group(#"Changed Type", {"Part"}, {{"A", each Table.AddIndexColumn(_, "Index",1,1), type table}})

2. Expand it

3. Add List.Generate function

= (values as list, grouping as list) as list =>
let
GRTList = List.Generate(
()=> [ GRT = values{0}, i = 0 ],
each [i] < List.Count(values),
each try
if grouping{[i]} = grouping{[i] + 1}
then if [GRT]>0 then [GRT = [GRT] + values{[i] + 1}, i = [i] + 1]
else [GRT = values{[i] + 1}, i = [i] + 1]
else [GRT = values{[i] + 1}, i = [i] + 1]
otherwise [i = [i] + 1],
each [GRT])
in
GRTList

List.Generate () is used to generate a List.
The first parameter of List.generate() in this example is a function with two variables,
The first variable GRT=values{0} is the initial value of the variable GRT used for accumulation, which is the value of the column [Net Quantity];
The second variable i=0 is used as an increasing sequence in the function, which represents the i-th row here. [GRT=300, i =0]
Passed to the second parameter is a conditional function to determine whether the current number of rows participating in the accumulation is less than the total number of rows of data.
If the conditions of the second parameter are met, pass to the third parameter --the rule to generate the result, here two if statements are used, the first if to determine whether the group is the same:
If it is the same group, perform the second judgment: whether the sum is greater than 0; If greater than 0, the sum is [300+(-150),1] -- >[300+(-150)+(-200),2]; Otherwise the reset returns the current value [-200,3];
If it's not the same group, that's when it's changed to group B, the result is reset.
The last parameter returns the list of GRT.

4. Combine columns into a table

= Table.FromColumns(
    {
      Source[Part], A[Index], Source[Net Quantity], Source[Date],
    FX(A[Net Quantity],A[Part])
    }, {
      "Part",
      "Index",
      "Net Quantity","Date",
       "Running Total"
    })

Build a table by column, combine the columns into the final result table.
The first parameter in FX is [Net Quantity] in A in the previous step as values.
The second parameter is [Part] in A in the previous step as grouping.

5. Output

Summary:
Through direct accumulation, conditional accumulation and group conditional accumulation, we can gradually understand the cyclic recursive function. Of course, we can also replace conditions and other functions according to different needs (not limited to accumulation).

Related links: 

https://community.powerbi.com/t5/Desktop/Running-total-by-group-with-a-reset/m-p/1961840#M744143 

Author: Yalan Wu

Reviewer: Ula Huang, Kerry Wang