Sum of range with aggregated data

Hi LukeReds 

You can put the following code to advanded editor in power query

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlbSUTKxABIBRfkpBoZAhpFSrA5CyhJZygRZytQAWcoQRcoQWcoCRcoIRZcBTM4EbAaylBGyFIouUxQXwh1vhNvxIClLpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Week = _t, Prod = _t, SellOut = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Week", Int64.Type}, {"Prod", type text}, {"SellOut", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Prod"}, {{"Data", each Table.AddIndexColumn(_,"Index",1,1), type table}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Year", "Week", "SellOut", "Index"}, {"Year", "Week", "SellOut", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Data", "Custom", each if [Index]>=4 then List.Sum(Table.SelectRows(#"Expanded Data",(x)=>x[Prod]=[Prod] and x[Index]<=[Index] and x[Index]>=[Index]-4+1)[SellOut]) else 0)
in
    #"Added Custom"

 

Output

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly