excelpowerbipowerqueryrolling-sum

Excel (or) Power BI, Rolling Sum

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.

enter image description here

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

enter image description here

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.

enter image description here

enter image description here

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

Solution

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

    enter image description here

    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

    enter image description here enter image description here

    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

    enter image description here

    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"

    enter image description here