Forum Discussion
Cumulative Sum in Power Query
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
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!