Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Cumulative sum Column

Hello Everyone!   I have a table like this, with the sales each week, i want to calculate a cumulative sum for each month.  The table has sales for each month: I have the table like this:    ...
Need Help
Show and Tell
Tips and Tricks
  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    Anonymous 

    you can create a column

    Column = CALCULATE(sum(Sheet6[ Sales Week]),FILTER(Sheet6,Sheet6[Period]=EARLIER(Sheet6[Period])&&Sheet6[Week]<=EARLIER(Sheet6[Week])))

    please see the attachment below

  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    Anonymous 

    please try this

    Column = CALCULATE(sum(Sheet6[ Sales Week]),FILTER(Sheet6,Sheet6[Period]=EARLIER(Sheet6[Period])&&Sheet6[Week]<=EARLIER(Sheet6[Week])&&'Sheet6'[Product]=EARLIER(Sheet6[Product])))
Ashish_Mathur's avatar
Ashish_Mathur
Super User
4 years ago

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Pond", type text}, {"Cycle", type text}, {"Raleo", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Merged Columns" = Table.CombineColumns(#"Added Index",{"Pond", "Cycle"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    BufferedTable = Table.Buffer(#"Merged Columns"),
    Custom1 = Table.AddColumn(
    BufferedTable, 
    "Running Total", 
    (OutTable) => 
        List.Sum(
            Table.SelectRows(
                BufferedTable, 
                (InTable) => InTable[Index] <= OutTable[Index]
                and 
                InTable[Merged] = OutTable[Merged])[Raleo]
        )
    ),
    #"Split Column by Delimiter" = Table.SplitColumn(Custom1, "Merged", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Merged.1", "Merged.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Merged.1", "Pond"}, {"Merged.2", "Cycle"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Running Total", Int64.Type}})
in
    #"Changed Type1"

Hope this helps.

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago

Thanks for all