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