Forum Discussion
Problem with Cumulative Total column
Running = SUMX(
FILTER( Sheet1,
Sheet1[Day Of Month]<=EARLIER(Sheet1[Day Of Month])
),
Sheet1[Column With Amount])....didn't know the name of the column with the amount -- - so made that up....
if this doesn't work please copy/type in a brief sample of the table itself to be viewed....
Hi,
You can create a calculated column to do it. See my screenshots below.
Running = SUMX(FILTER( Sheet1, Sheet1[Day Of Month]<=EARLIER(Sheet1[Day Of Month])), Sheet1[Column With Amount])
Or you also can use a measure to achieve it.
Measure = CALCULATE(sum(Sheet1[Column With Amount]), FILTER(Sheet1, Sheet1[Day Of Month] <= MAX(Sheet1[Day Of Month])))
The difference between measure and calculated column is that a calculated column is just like any other column in a table and you can use it in rows, columns, filters, or values of a pivot table or any other report. While measure is an aggregated values from many rows in a table. The value of a calculated column is computed during data refresh and uses the current row as a context; A measure operates on aggregations of data defined by the current context.
For more details about calculated column and measure, you can see here.
BTW, for resolving “Sheet1” name issue, you may already have existing object named “Sheet1”. You should change it to another name.
Best Regards
Alex
Running = SUMX(
FILTER( Sheet1,
Sheet1[Day Of Month]<=EARLIER(Sheet1[Day Of Month])
),
Sheet1[Column With Amount])
....didn't know the name of the column with the amount -- - so made that up....
if this doesn't work please copy/type in a brief sample of the table itself to be viewed....
I get an error saying SUMX not recognizedHere is my table
But I see in your pasted screen shot it has check mark and 'no syntax problem detected'....
your interface is different than mine - you are in Query Editor
try this - in Desktop, not query editor but just regular Data view (icon in left frame), select Table in Fields frame right, then in Modeling tab there is the 'New Column' choice.... and one offers the expression field just below the ribbon....
If run this code from the desktop I get Failed to resolve name Sheet1. This may be from my new table format.
My table now looks like this as I'll need to only see the cumulative totals based on each month.
My future concern is what to do when it gets to today's date. In the current month I would want the cumulative total to stop after the last non 0 value. In this case September 8th would be NULL. Also, I did a find and replace on null with 0. I may need to put this back.