Calculate running sum of previous n rows

[PaisleyPrince]

Hi,

I'm trying to create a loop in power query to calculate the total of the previous 8 rows as per the screenshot below. Any advice would be much appreciated.

thanks

Scott

[shafiz_p · Accepted solution]

Hi @PaisleyPrince  Try this in custom column:

= if [Index] < 8 then "" else List.Sum(List.Range(#"Changed Type"[Value], [Index] - 8, 8))

See image:

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[AlienSx · Accepted solution]

this one works 100 times (literally - one hundred times) faster on a small set of 5000 rows.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 
    values = List.Buffer(Source[Value]),
    rt = List.Generate(
        () => [i = 0, ttl = values{0}],
        (x) => x[i] < List.Count(values),
        (x) => [i = x[i] + 1, ttl = x[ttl] + values{i}],
        (x) => x[ttl]
    ), 
    last_n = List.Transform(List.Zip({rt, List.Repeat({null}, 8 - 1) & {0} & List.RemoveLastN(rt, 8)}), (x) => x{0} - x{1}),
    result = Table.FromColumns(Table.ToColumns(Source) & {last_n}, Table.ColumnNames(Source) & {"Running Sum"})
in
    result

[slorin · Accepted solution]

Hi @PaisleyPrince 

Adapt @shafiz_p's  solution with List.Buffer

simplier but twice slower than @AlienSx 's proposition with 20000 rows

let
Source = YourSource,
Values = List.Buffer(Source[Value]),
#"Running Sum" = Table.AddColumn(Source, "Running Sum",
  each if [Index] < 8 then "" else List.Sum(List.Range(Values, [Index] - 8, 8)))
in
#"Running Sum"

Stéphane

[AlienSx · Accepted solution]

this one works 100 times (literally - one hundred times) faster on a small set of 5000 rows.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 
    values = List.Buffer(Source[Value]),
    rt = List.Generate(
        () => [i = 0, ttl = values{0}],
        (x) => x[i] < List.Count(values),
        (x) => [i = x[i] + 1, ttl = x[ttl] + values{i}],
        (x) => x[ttl]
    ), 
    last_n = List.Transform(List.Zip({rt, List.Repeat({null}, 8 - 1) & {0} & List.RemoveLastN(rt, 8)}), (x) => x{0} - x{1}),
    result = Table.FromColumns(Table.ToColumns(Source) & {last_n}, Table.ColumnNames(Source) & {"Running Sum"})
in
    result

[v-junyant-msft]

Hi @PaisleyPrince ,

It looks like shafiz_p's answer can help you solve your problem. If solved please accept the reply in this post which you think is helpful as a solution to help more others facing the same problem to find a solution quickly, thank you very much!

Best Regards,
Dino Tao

[Omid_Motamedise]

= if [Index] < 8 then "" else List.Sum(List.LastN(List.FirstN(Source[Value], [Index] ), 8))

[shafiz_p · Accepted solution]

Hi @PaisleyPrince  Try this in custom column:

= if [Index] < 8 then "" else List.Sum(List.Range(#"Changed Type"[Value], [Index] - 8, 8))

See image:

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz