Cumulative Sum in Power Query

[hemann]

Hi, 

I am trying to perform a cumulative sum in Power Query. Below is an example of the data and the deisred result. Can somebody please provide M language logic in Power Query to solve this. 

Current Data

End of Month	Attribute	Value
31/12/2014	Product A	20
31/12/2014	Product B	2290
31/12/2014	Product C	0
31/12/2014	Product D	0
31/12/2014	Product E	0
31/12/2014	Product F	0
31/05/2015	Product A	3
31/05/2015	Product B	528
31/05/2015	Product C	0
31/05/2015	Product D	0
31/05/2015	Product E	0
31/05/2015	Product F	0
31/03/2015	Product A	10
31/03/2015	Product B	1472
31/03/2015	Product C	1
31/03/2015	Product D	0
31/03/2015	Product E	0
31/03/2015	Product F	0
31/01/2015	Product A	19
31/01/2015	Product B	4084
31/01/2015	Product C	1
31/01/2015	Product D	0
31/01/2015	Product E	0
31/01/2015	Product F	0
30/04/2015	Product A	5
30/04/2015	Product B	946
30/04/2015	Product C	1
30/04/2015	Product D	0
30/04/2015	Product E	0
30/04/2015	Product F	0
28/02/2015	Product A	16
28/02/2015	Product B	2492
28/02/2015	Product C	1
28/02/2015	Product D	0
28/02/2015	Product E	0
28/02/2015	Product F	0

Desired Result (Cumulative Sum)

End of Month	Attribute	Value	Cumulative Sum
31/12/2014	Product A	20	20
31/12/2014	Product B	2290	2290
31/12/2014	Product C	0	0
31/12/2014	Product D	0	0
31/12/2014	Product E	0	0
31/12/2014	Product F	0	0
31/05/2015	Product A	3	23
31/05/2015	Product B	528	2818
31/05/2015	Product C	0	0
31/05/2015	Product D	0	0
31/05/2015	Product E	0	0
31/05/2015	Product F	0	0
31/03/2015	Product A	10	33
31/03/2015	Product B	1472	4290
31/03/2015	Product C	1	1
31/03/2015	Product D	0	0
31/03/2015	Product E	0	0
31/03/2015	Product F	0	0
31/01/2015	Product A	19	52
31/01/2015	Product B	4084	8374
31/01/2015	Product C	1	2
31/01/2015	Product D	0	0
31/01/2015	Product E	0	0
31/01/2015	Product F	0	0
30/04/2015	Product A	5	57
30/04/2015	Product B	946	9320
30/04/2015	Product C	1	3
30/04/2015	Product D	0	0
30/04/2015	Product E	0	0
30/04/2015	Product F	0	0
28/02/2015	Product A	16	73
28/02/2015	Product B	2492	11812
28/02/2015	Product C	1	4
28/02/2015	Product D	0	0
28/02/2015	Product E	0	0
28/02/2015	Product F	0	0

[Royel]

Hi @hemann your shared results Date colum is not sorted properly. 

However you can try this m-code to get the cumulative sum 

let
    Source = Excel.Workbook(File.Contents("C:\Users\RejaulIslamRoyel\Desktop\Cumulative Sum in Power Query.xlsx"), null, true),
    Sheet3_Sheet = Source{[Item="Sheet3",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet3_Sheet, [PromoteAllScalars=true]),
    
    // Fix: Handle DD/MM/YYYY date format conversion
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{
        {"End of Month", type text},  // Keep as text first
        {"Attribute", type text}, 
        {"Value", Int64.Type}
    }),
    
    // Convert DD/MM/YYYY text to proper date
    #"Converted Dates" = Table.TransformColumns(#"Changed Type", {
        {"End of Month", each Date.FromText(_, [Format="dd/MM/yyyy"]), type date}
    }),
    #"Sorted Rows" = Table.Sort(#"Converted Dates",{{"End of Month", Order.Ascending}}),
    
    // Step 2: Add Index column (required for cumulative calculation)
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
    
    // Step 3: Add Cumulative Sum column
    #"Added Cumulative Sum" = Table.AddColumn(#"Added Index", "Cumulative Sum", 
        each List.Sum(
            List.FirstN(
                Table.SelectRows(#"Added Index", 
                    (row) => row[Attribute] = [Attribute] and row[Index] <= [Index]
                )[Value], 
                [Index] + 1
            )
        ), 
        type number
    ),
    
    // Step 4: Remove helper Index column
    #"Removed Index" = Table.RemoveColumns(#"Added Cumulative Sum", {"Index"})
in
    #"Removed Index"

Note: Update source with your Current Data 

Output: 

Did it Helped? ✔ Give a Kudo • Mark as Solution – help others too! 

[ryan_mayu · Accepted solution]

@hemann 

It looks like that the data is not accumulating by date. Not sure if this is what you want.

If you want to accumulate by date, you can try this

= Table.AddColumn(#"Added Index", "Custom", each List.Sum( Table.SelectRows(#"Added Index",(x)=>x[End of Month]<=[End of Month] and x[Attribute]=[Attribute] )[Value]))

if just need to accumulate by the row order, you need to create a index column , then try this

= Table.AddColumn(#"Added Custom", "Custom.1", each List.Sum( Table.SelectRows(#"Added Custom",(x)=>x[Index]<=[Index] and x[Attribute]=[Attribute] )[Value]))

pls see the attachment below

[hemann]

Yes I wanted it to accumulate on date and product. Your first solution worked - thanks legend!!

[ryan_mayu]

you are welcome

[jaineshp]

Hey @hemann,

Here's the solution for calculating cumulative sum in Power Query using M language:

Solution Steps

Step 1: Sort Your Data First, ensure your data is properly sorted by Date and Attribute:

#"Sorted Rows" = Table.Sort(#"Previous Step",{{"End of Month", Order.Ascending}, {"Attribute", Order.Ascending}})

Step 2: Group by Attribute Group the data by Attribute to calculate cumulative sum for each product separately:

#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Attribute"}, {
{"Data", each _, type table}
})

Step 3: Add Cumulative Sum Column Within each group, add the cumulative sum calculation:

#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each
Table.AddIndexColumn(
Table.AddColumn([Data], "Cumulative Sum",
each List.Sum(List.FirstN([Data][Value], [Index] + 1))
), "Index", 0
)
)

Step 4: Alternative Simpler Approach You can also use this more straightforward method by adding an index first:

Source = your_table,
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
#"Added Custom Column" = Table.AddColumn(#"Added Index", "Cumulative Sum",
each List.Sum(
List.FirstN(
Table.SelectRows(#"Added Index",
each [Attribute] = #"Added Index"{[Index]}[Attribute] and
[Index] <= #"Added Index"{[Index]}[Index]
)[Value],
[Index] + 1
)
)
)

Step 5: Clean Up Remove the helper Index column and expand the results:

#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"End of Month", "Attribute", "Value", "Cumulative Sum"})

This approach will give you the exact cumulative sum pattern shown in your desired result, where each product maintains its own running total across different time periods.

Fixed? ✓ Mark it • Share it • Help others!

Best Regards,
Jainesh Poojara | Power BI Developer