Excel (or) Power BI, Rolling Sum

Question

Is there any way in Excel Pivot or Power BI to do the rolling sum of the given data (let say monthly)?

Let say I have a list of cases, each row represent case count and amount. The project start date and end date varied as follows.

For, simplicity, if I demonstrate the data graphically, would be as follows.

What I'm try to do is to aggregate how much case counts and amounts in total for each chunk of month.

My goal is to produce below list using Pivot (if Pivot is not possible, then by Power Query) directly.

I could produce monthly aggregates using Filter function and Sum, then pivot that data to produce above result.

If there is a direct way of producing that aggregates in one step, that would be better. Please suggest it for me.

Please see sample data in below link

https://docs.google.com/spreadsheets/d/1vAKElb2-V_If-MMlPwHk_VGhYr8pkOg_gQfRYRrkbtc/edit?usp=share_link

Excel file in Zip

https://drive.google.com/file/d/1QqgNUrJlBuvin7iecsxsvexrGZXFIt-g/view?usp=share_link

Thank you in advance

LuZ

Did you check out the build in Quick Measure "Rolling Average", which should be fairly easy to adjust for you rather unconventional rolling sum, or what else did you try yourself? — Peter, Jan 30 '23 at 14:58
What is even the calculation? Are you dividing Amount by 12 then counting that in each of the months for each row? — horseyride, Jan 30 '23 at 15:11
@Peter, Rolling Average is interesting. But in my case the date for each chunk of month is somewhat like:- ([My PickUp Date] >= [Start Date]) * ([My PickUp Date] <= [End Date]) — LuZ, Jan 30 '23 at 15:17
@horseyride No, I will pick up the whole amount. For example, if case A1 triggered, I will pick 217 for amount. — LuZ, Jan 30 '23 at 15:19

horseyride · Accepted Answer · 2023-02-21T21:58:55.780

You can load the data into powerquery and transform from left to data table on right

code for that is

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom1" = Table.AddColumn(Source, "Date", each List.Generate(()=>[x=[Start Date],i=0], each [i]<12, each [i=[i]+1,x=Date.AddMonths([x],1)], each [x])),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom1", "Date"),
#"Added Custom" = Table.AddColumn(#"Expanded Custom", "Year", each Date.Year([Date])),
#"Added Custom2" = Table.AddColumn(#"Added Custom", "Month", each Date.Month([Date])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Start Date", "End Date", "Date"})
in  #"Removed Columns"

Afterwards, load the powerquery back into excel as pivot report and generate your table

Alternatively, just use use

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom1" = Table.AddColumn(Source, "Date", each List.Generate(()=>[x=[Start Date],i=0], each [i]<12, each [i=[i]+1,x=Date.AddMonths([x],1)], each [x])),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom1", "Date"),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Start Date", "End Date"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Date"}, {{"Amount", each List.Sum([Amount]), type number}, {"Case Count", each List.Sum([Case Count]), type number}}),
#"Changed Type" = Table.TransformColumnTypes(#"Grouped Rows",{{"Date", type date}, {"Amount", type number}, {"Case Count", type number}})
in   #"Changed Type"

to generate this table, then graph it

EDIT: Use the End Date instead of assuming end date is 12 months after start date

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

#"Added Custom" = Table.AddColumn(Source,"Date", each 
    let
        begin = Date.StartOfMonth([Start Date])
    in
        List.Accumulate(
            {0..(Date.Year([End Date])-Date.Year([Start Date]))*12+(Date.Month([End Date])-Date.Month([Start Date]))},
            {},
            (a,b) => a&{Date.AddMonths(begin,b)}
        )
),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Date"),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Start Date", "End Date"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Date"}, {{"Amount", each List.Sum([Amount]), type number}, {"Case Count", each List.Sum([Case Count]), type number}}),
#"Changed Type" = Table.TransformColumnTypes(#"Grouped Rows",{{"Date", type date}, {"Amount", type number}, {"Case Count", type number}})
in   #"Changed Type"

Thanks! That will look into it and let me come back with unclear points. — LuZ, Feb 02 '23 at 07:10
The duration are not always 12 months, how should I limit this not to more than End Date in each row? #"Added Custom1" = Table.AddColumn(Source, "Date", each List.Generate(()=>[x=[Start Date],i=0], each [i]<12, each [i=[i]+1,x=Date.AddMonths([x],1)], each [x])), — LuZ, Feb 20 '23 at 00:23

Excel (or) Power BI, Rolling Sum

1 Answers1