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: 

 

PeriodNo. WeekWeekBusiness Sales Week
1/2/2021 10 - 28/Feb - 28/Feb10Harinas-Industrial                        250.00
1/2/2021 09 - 21/Feb - 27/Feb9Harinas-Industrial            1,602,210.00
1/2/2021 08 - 14/Feb - 20/Feb8Harinas-Industrial                630,410.00
1/2/2021 07 - 7/Feb - 13/Feb7Harinas-Industrial                876,720.00
1/2/2021 06 - 1/Feb - 6/Feb6Harinas-Industrial                616,965.00

 

I need a table that look like this: 

PeriodNo. WeekWeekBusiness Sales Week Cumulative Sales
1/2/2021 10 - 28/Feb - 28/Feb10Harinas-Industrial250.00 3,726,555.00
1/2/202109 - 21/Feb - 27/Feb9Harinas-Industrial1,602,210.00 3,726,305.00
1/2/202108 - 14/Feb - 20/Feb8Harinas-Industrial630,410.00 2,124,095.00
1/2/202107 - 7/Feb - 13/Feb7Harinas-Industrial876,720.00 1,493,685.00
1/2/202106 - 1/Feb - 6/Feb6Harinas-Industrial                616,965.00     616,965.00

 

Where the cumulative colum is a sum of the sales each week

 

Hope someone could help me!

 

 

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])))

10 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hello ryan_mayu thanks for the answer

       

      The thing is actually that my table looks like this: 

       

      and the result should be:

      The cumulative should calculate for each product

       

      Thanks!

      • ryan_mayu's avatar
        ryan_mayu
        Super User
        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])))
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

    I have a way to achieve your need . Calculate the cumulative amount of different products each month.

     

    Cumulative Sales = CALCULATE(SUM('Table'[Week Sales]),FILTER('Table','Table'[Product]=EARLIER('Table'[Product]) && 'Table'[Week Num]<=EARLIER('Table'[Week Num])))

     

    'Table'[Product]=EARLIER('Table'[Product])  to make sure the product is same .

     

    The effect is as shown:

    Notice: the Data type of [Week Sales] must be “number” not “text”

     

    Best Regards

    Community Support Team _ Ailsa Tao

     

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

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    4 years ago

    Hello everyone sorry for the hassle I need help with a column accumulated in power query for a dashboard in power bi where I need a column (Runnig t) adding the values (ve) depending on the variables (Est) and (CICL) as I show in the example. for your help I am grateful.

    Jcarofi_0-1631133496062.png

    Recueden I need the code in M language

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      4 years ago

      Hi,

      Share data in a format that can be pasted in an MS Excel file.

      • Syndicate_Admin's avatar
        Syndicate_Admin
        Administrator
        4 years ago

        PondCycleRaleo
        LEB5C2-202132
        LEB5C2-203554
        LEB5C2-204549
        LEB12C2-201653
        LEB12C2-203203
        LEB12C2-204339
        LEB5C1-213152
        LEB5C1-214063
        LEB5C1-215618

        Mcode

        let
        Origen = Excel.Workbook(File.Contents("C:\Users\J024919\Downloads\Prub.xlsx"), null, true),
        Est_Table = Origen{[Item="Est",Kind="Table"]}[Data],
        #"Tipo cambiado" = Table.TransformColumnTypes(Est_Table,{{"Estanque", type text}, {"Ciclo", type text}, {"Raleo", type number}}),
        TableType = Value.Type(Table.AddColumn(#"Tipo cambiado", "Running Sum", each null, type number)),
        #"Grouped Rows" = Table.Group(Est_Table, {"Estanque", "Ciclo"}, {{"AllData", each fnAddRunningSum (_, "Raleo"), TableType}}),
        #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Raleo", "Running Sum"}, {"Raleo", "Running Sum"})
        in #"Expanded AllData"

        Code function =

        (MyTable as table, value as text) as table =>
        let
        Source = Table.Buffer(Est),
        value = "Ciclo",
        TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
        Cumulative = List.Skip(List.Accumulate(Table.Column(Source, value),{0},(cumulative,cost) => cumulative & {List.Last(cumulative) + Number.From(cost)})),
        AddedRunningSum = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType)
        in
        AddedRunningSum

        I don´t Know what is wrong.

        Help me please