Forum Discussion
Calculation based on Previous Row / Month
Hi AdamPBIDev
List.Accumulate can do it
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdFbDoQgDEDRvZDMn4OllNdajPvfhjhgax3kRxJzQut12wy4FfyKgGgW4wDqEy3Ap54eKNcDzL40R9p9qcPzDaOgURCDbKI2SYxnk96HEaOsURYT2JTHRV5QvJCDcYJzOTZOm9tCmQ0+hkVBhTfi3vhDzqbWm95647U5TXpj3wonvbH/kzLp/T9s0LuhaPOkd78IbZz01gnGvZu5LTTo3YcF+fzaez8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Month Year" = _t, Code = _t, #"Activity Change %" = _t, Activity = _t, Index = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month Year", type date}, {"Code", Int64.Type}, {"Activity Change %", Percentage.Type}, {"Activity", Int64.Type}, {"Index", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [ CurIndex=[Index], CurCode=[Code], CurTable=Table.SelectRows(#"Changed Type",each [Code]=CurCode), Initial=CurTable[Activity]{0} * (1+CurTable[#"Activity Change %"]{0}), a=if CurIndex=0 then Initial else List.Accumulate(List.RemoveFirstN( Table.SelectRows(CurTable,each [Index]<=CurIndex )[#"Activity Change %"],1),Initial,(s,c)=>s+s*c)][a]) in #"Added Custom"
Hi AdamPBIDev
List.Accumulate can do it
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdFbDoQgDEDRvZDMn4OllNdajPvfhjhgax3kRxJzQut12wy4FfyKgGgW4wDqEy3Ap54eKNcDzL40R9p9qcPzDaOgURCDbKI2SYxnk96HEaOsURYT2JTHRV5QvJCDcYJzOTZOm9tCmQ0+hkVBhTfi3vhDzqbWm95647U5TXpj3wonvbH/kzLp/T9s0LuhaPOkd78IbZz01gnGvZu5LTTo3YcF+fzaez8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Month Year" = _t, Code = _t, #"Activity Change %" = _t, Activity = _t, Index = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Month Year", type date}, {"Code", Int64.Type}, {"Activity Change %", Percentage.Type}, {"Activity", Int64.Type}, {"Index", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [
CurIndex=[Index],
CurCode=[Code],
CurTable=Table.SelectRows(#"Changed Type",each [Code]=CurCode),
Initial=CurTable[Activity]{0} * (1+CurTable[#"Activity Change %"]{0}),
a=if CurIndex=0 then Initial else
List.Accumulate(List.RemoveFirstN( Table.SelectRows(CurTable,each [Index]<=CurIndex )[#"Activity Change %"],1),Initial,(s,c)=>s+s*c)][a])
in
#"Added Custom"
Anonymous Thank you so much for this solution & introducing me to list.accumulate! Works a treat and is a much more elegant solution than my approach!
I can broadly follow the syntax, would you mind explaining where you define the s & c variable in this portion of the code i.e. how does it know to reference activity & activity change?
Thanks for your help!
In case anyone else is interested, this is an alternative solution which also seems to solve the problem (although not as neatly as Vera!)
BufferedStart = Table.Buffer( Table.Sort(Custom,{{"Code", Order.Ascending},{"Index", Order.Ascending}}) ),
Initial = BufferedStart{0},
ListGenerate = List.Generate( ()=>
[Result = Initial[Starting Forecast], Counter = 0],
each [Counter] < Table.RowCount(BufferedStart),
each [
Result = if BufferedStart[Index]{Counter} = 0 then BufferedStart[Starting Forecast]{Counter} else [Result] + ([Result] * BufferedStart[#"Activity Change %"]{Counter}),
Counter = [Counter] + 1
],
each [Result]
),
Custom2 = Table.FromColumns(Table.ToColumns(BufferedStart) & {ListGenerate} , Table.ColumnNames(BufferedStart) & {"Result"}),
Hi AdamPBIDev
List.Generate is also very cool! I guess you understand s & c, the list does not contain the first one when you are using Activity (the variable Initial, also as the seed), the rest are all Activity Change %...