How to create a running total/cumulative sum

We have a built in function that works perfectly for this: CumulativeSum.

In my example, I want to create a monthly running total of the [Cost] column in chronological order to plot in a line chart.

I sorted the [Month of Date] column ascending and grouped by the [Month of Date] column. I then created an aggregate calculation to Sum() the [Cost] column so I could get the [Monthly Cost]. Finally, I created an aggregate calculation to CumulativeSum() the [Monthly Cost] column, outputting the running total.

I could then use this [CumulativeSum of Monthly Cost] column as the Y-axis for my line chart.

Important note: cumulative functions depend on the order of the given column. So if you change the sorting of that column, you could change the result!


Hi Gareth,

What if one of the date groupings has zero records in it after a certain date? Say I have records for January through June, with a nice slope up like your graph. For months past June, I have no records, but I’d like to display the cumulativesum as just a flat line forward at that point. Is it possible?

See also this thread for context to my situation. I am thinking it can only be solved with a date spine, but have not yet found time to explore.

Joining in a CSV of dates or generating dates with SQL is the solution for adding missing dates.
I think ‘CumulativeSum’ should work to fill in those dates. If not, ‘FillDown’ might do what you need.

1 Like