Power query acumulate sum formula

[emema0]

Im working in power query, I need to acumulate value per month and account. For january I like to only se value for janauray and then for february summed value for jan and feb and so on.
Is there any formula that ca create the coulumn Acum Value below?


[TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD]Account[/TD]
[TD]Period[/TD]
[TD]Value[/TD]
[TD]Acum Value[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]Jan[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]Feb[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]Mar[/TD]
[TD]5[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]601[/TD]
[TD]Jan[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]601[/TD]
[TD]Feb[/TD]
[TD]1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]601[/TD]
[TD]Mar[/TD]
[TD]2[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]Jan[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]Feb[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]Mar[/TD]
[TD]2[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]

 

[sandy666]

is that what you want?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Account[/td][td=bgcolor:#5B9BD5]Period[/td][td=bgcolor:#5B9BD5]Value[/td][td][/td][td=bgcolor:#70AD47]Account[/td][td=bgcolor:#70AD47]Period[/td][td=bgcolor:#70AD47]Value[/td][td=bgcolor:#70AD47]RunningTotStartOver[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

123​

[/td][td=bgcolor:#DDEBF7]Jan[/td][td=bgcolor:#DDEBF7]

2​

[/td][td][/td][td=bgcolor:#E2EFDA]

123​

[/td][td=bgcolor:#E2EFDA]Jan[/td][td=bgcolor:#E2EFDA]

2​

[/td][td=bgcolor:#E2EFDA]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

123​

[/td][td]Feb[/td][td]

2​

[/td][td][/td][td]

123​

[/td][td]Feb[/td][td]

2​

[/td][td]

4​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

123​

[/td][td=bgcolor:#DDEBF7]Mar[/td][td=bgcolor:#DDEBF7]

5​

[/td][td][/td][td=bgcolor:#E2EFDA]

123​

[/td][td=bgcolor:#E2EFDA]Mar[/td][td=bgcolor:#E2EFDA]

5​

[/td][td=bgcolor:#E2EFDA]

9​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

601​

[/td][td]Jan[/td][td]

4​

[/td][td][/td][td]

601​

[/td][td]Jan[/td][td]

4​

[/td][td]

4​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

601​

[/td][td=bgcolor:#DDEBF7]Feb[/td][td=bgcolor:#DDEBF7]

1​

[/td][td][/td][td=bgcolor:#E2EFDA]

601​

[/td][td=bgcolor:#E2EFDA]Feb[/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]

5​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

601​

[/td][td]Mar[/td][td]

2​

[/td][td][/td][td]

601​

[/td][td]Mar[/td][td]

2​

[/td][td]

7​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

100​

[/td][td=bgcolor:#DDEBF7]Jan[/td][td=bgcolor:#DDEBF7]

1​

[/td][td][/td][td=bgcolor:#E2EFDA]

100​

[/td][td=bgcolor:#E2EFDA]Jan[/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

100​

[/td][td]Feb[/td][td]

5​

[/td][td][/td][td]

100​

[/td][td]Feb[/td][td]

5​

[/td][td]

6​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

100​

[/td][td=bgcolor:#DDEBF7]Mar[/td][td=bgcolor:#DDEBF7]

2​

[/td][td][/td][td=bgcolor:#E2EFDA]

100​

[/td][td=bgcolor:#E2EFDA]Mar[/td][td=bgcolor:#E2EFDA]

2​

[/td][td=bgcolor:#E2EFDA]

8​

[/td][/tr]
[/table]

M-code for table

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TableType = Value.Type(Table.AddColumn(Source, "RunningTotStartOver", each null, type number)),
    #"Grouped Rows" = Table.Group(Source, {"Account"}, {{"AllData", fnRunningTotStartOver, TableType}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Period", "Value", "RunningTotStartOver"},{"Period", "Value", "RunningTotStartOver"})
in
    #"Expanded AllData"[/SIZE]

M-code for function

[SIZE=1](MyTable as table) as table =>
let
    Source = Table.Buffer(MyTable),
    TableType = Value.Type(Table.AddColumn(Source, "RunningTotStartOver", each null, type number)),
    Cumulative = List.Skip(List.Accumulate(Source[Value],{0},(Cumulative,Value) => Cumulative & {List.Last(Cumulative) + Value})),
    AddedRunningTotStartOver = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType)
in
    AddedRunningTotStartOver[/SIZE]

 

[emema0]

Thank you Sandy, works out exactly the way I want!

 

[sandy666]

you are welcome

have a nice day