power query grouping by name and sort and cumulative

[theopa]

hi all.

i have a question about some product table

there are 
[Prodcode], [porductNM] , [Date], [salesamount], [salesprice]

that i want is addcolumns which one value is cumulative value with [salesamount] and [salesprice]
with date
if change next year -> reset cumulative

for example 

[porductNM] , [Date], [salesamount], [salesprice]

candy | 2021-01-01 | 3 |  2 |

cookie | 2021-01-01 | 5 | 4 |

chocolate | 2021-01-01 | 7 | 6 |

candy | 2021-02-01 | 3 |  2 |

cookie | 2021-02-01 | 5 | 4 |

chocolate | 2021-02-01 | 7 | 6 |

candy | 2021-03-01 | 3 |  2 |

cookie | 2021-03-01 | 5 | 4 |

chocolate | 2021-03-01 | 7 | 6 |

candy | 2022-01-01 | 3 |  2 |

cookie | 2022-01-01 | 5 | 4 |

chocolate | 2022-01-01 | 7 | 6 |

candy | 2022-02-01 | 3 |  2 |

cookie | 2022-02-01 | 5 | 4 |

chocolate | 2022-02-01 | 7 | 6 |

candy | 2022-03-01 | 3 |  2 |

cookie | 2022-03-01 | 5 | 4 |

chocolate | 2022-03-01 | 7 | 6 |

=>

[porductNM] , [Date], [salesamount], [salesprice], [Csalesamount], [Csalesprice]

candy | 2021-01-01 | 3 |  2 | 3 | 2

cookie | 2021-01-01 | 5 | 4 | 5 | 4

chocolate | 2021-01-01 | 7 | 6 | 7 | 6

candy | 2021-02-01 | 3 |  2 | 6 | 4

cookie | 2021-02-01 | 5 | 4 | 10 | 8

chocolate | 2021-02-01 | 7 | 6 | 14 | 12

candy | 2021-03-01 | 3 |  2 | 9 | 6

cookie | 2021-03-01 | 5 | 4 | 15 | 12

chocolate | 2021-03-01 | 7 | 6 | 21 | 18

candy | 2022-01-01 | 3 |  2 | 3 | 2

cookie | 2022-01-01 | 5 | 4 | 5 | 4

chocolate | 2022-01-01 | 7 | 6 | 7 | 6

candy | 2022-02-01 | 3 |  2 | 6 | 4

cookie | 2022-02-01 | 5 | 4 | 10 | 8

chocolate | 2022-02-01 | 7 | 6 | 14 | 12

candy | 2022-03-01 | 3 |  2 | 9 | 6

cookie | 2022-03-01 | 5 | 4 | 15 | 12

chocolate | 2022-03-01 | 7 | 6 | 21 | 18

[AlienSx · Accepted solution]

hi, @theopa 

    sort = Table.Sort(your_table, {"porductNM", "Date"}),
    f = (t as table) as table => 
        [rec = List.Buffer(Table.ToRecords(t)),
        gen = List.Generate(
            () => [i = 0, r = rec{0}, rt = [Csalesamount = r[salesamount], Csalesprice = r[salesprice]]],
            (x) => rec{x[i]}? <> null,
            (x) => 
                [i = x[i] + 1, r = rec{i}, 
                rt = 
                    [Csalesamount = x[rt][Csalesamount] + r[salesamount],
                    Csalesprice = x[rt][Csalesprice] + r[salesprice]]
                ],
            (x) => x[r] & x[rt]
        ),
        z = Table.FromRecords(gen)][z],
    g = Table.Group(
        sort, {"porductNM", "Date"},
        {{"rows", each f(Table.Sort(_, "Date"))}},
        GroupKind.Local,
        (s, c) => Byte.From(Date.Year(s[Date]) <> Date.Year(c[Date]) or (s[porductNM] <> c[porductNM]))),
    expand = Table.ExpandTableColumn(g, "rows", {"salesamount", "salesprice", "Csalesamount", "Csalesprice"})

[theopa]

thank you a lot

it's very good solution