Cumulative sum Column

[Anonymous]

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: 

Period	No. Week	Week	Business	Sales Week
1/2/2021	10 - 28/Feb - 28/Feb	10	Harinas-Industrial	250.00
1/2/2021	09 - 21/Feb - 27/Feb	9	Harinas-Industrial	1,602,210.00
1/2/2021	08 - 14/Feb - 20/Feb	8	Harinas-Industrial	630,410.00
1/2/2021	07 - 7/Feb - 13/Feb	7	Harinas-Industrial	876,720.00
1/2/2021	06 - 1/Feb - 6/Feb	6	Harinas-Industrial	616,965.00

I need a table that look like this: 

Period	No. Week	Week	Business	Sales Week	Cumulative Sales
1/2/2021	10 - 28/Feb - 28/Feb	10	Harinas-Industrial	250.00	3,726,555.00
1/2/2021	09 - 21/Feb - 27/Feb	9	Harinas-Industrial	1,602,210.00	3,726,305.00
1/2/2021	08 - 14/Feb - 20/Feb	8	Harinas-Industrial	630,410.00	2,124,095.00
1/2/2021	07 - 7/Feb - 13/Feb	7	Harinas-Industrial	876,720.00	1,493,685.00
1/2/2021	06 - 1/Feb - 6/Feb	6	Harinas-Industrial	616,965.00	616,965.00

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

Hope someone could help me!

[ryan_mayu · Accepted solution]

@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

[Syndicate_Admin]

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.

Recueden I need the code in M language

[Ashish_Mathur]

Hi,

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

[Syndicate_Admin]

Pond	Cycle	Raleo
LEB5	C2-20	2132
LEB5	C2-20	3554
LEB5	C2-20	4549
LEB12	C2-20	1653
LEB12	C2-20	3203
LEB12	C2-20	4339
LEB5	C1-21	3152
LEB5	C1-21	4063
LEB5	C1-21	5618

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

[Ashish_Mathur]

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]

Thanks for all

[v-yetao1-msft]

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.

[ryan_mayu · Accepted solution]

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

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 · Accepted solution]

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

[selimovd]

Hey @Anonymous ,

I think the TOTALMTD function can solve your problem:

TOTALMTD function (DAX) - DAX | Microsoft Docs

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi