Incremental summing of values

[sprasad]

I have this table with Date, State and CountState. I want to know how to calculate the cummulative values in the "Incremeatal Values Date wise" based on the "State" so that I can prepare the graph as shown below:

Date	State	CountState	Incremental Values Date wise
18-Feb-19	Active	2	2
19-Feb-19	Active	1	3
07-Feb-19	Active	2	5
14-Feb-19	Active	1	6
10-Jan-19	Active	1	7
31-Jan-19	Active	1	2
04-Jan-18	Closed	1	1
03-Nov-17	Closed	1	2
23-Oct-17	Closed	1	3
12-Dec-17	Closed	1	4
07-Nov-17	Closed	1	5
26-Feb-18	Closed	1	6
17-Nov-17	Closed	5	11
02-Nov-17	Closed	1	12
31-Oct-17	Closed	3	15
19-Oct-17	Closed	2	17
04-Dec-17	Closed	1	18
05-Dec-17	Closed	1	19

[PattemManohar]

@sprasad  The expected column name was contradicting with the requirement statement. That's why, I'm provding two options.

1. If you want the Running Total based on State by Date Wise. Add a New Column as below

RunningTotalByStateDate = 
VAR _CurrentCount = Test211RunningTotal[CountState]
VAR _PreviousCount = CALCULATE(SUM(Test211RunningTotal[CountState]),FILTER(ALL(Test211RunningTotal),Test211RunningTotal[Date]<=EARLIER(Test211RunningTotal[Date]) && Test211RunningTotal[State] = EARLIER(Test211RunningTotal[State])))
RETURN _PreviousCount 

2. If you want the Running Total based on State itself without any order (based on the default order of the data present). Then add an Index column in Power Query Editor and add a new column in Modeling Tab (DAX) as below

RunningTotalByState = 
VAR _CurrentCount = Test211RunningTotal[CountState]
VAR _PreviousCount = CALCULATE(SUM(Test211RunningTotal[CountState]),FILTER(ALL(Test211RunningTotal),Test211RunningTotal[Index]<=EARLIER(Test211RunningTotal[Index]) && Test211RunningTotal[State] = EARLIER(Test211RunningTotal[State])))
RETURN _PreviousCount 

[sprasad]

I think I sent the wrong table. 

Now if you can see we have the State Column having the value as New,  Closed and Active. Project has value as project A, Project M, project B. So I want the "Running Total by State & Project"

MergedDate - Copy	State	Project	CountState	Running Total By State & Project
28-Sep-17	New	Project A	1	1
23-Oct-17	Closed	Project M	1	1
26-Jan-18	Closed	Project M	1	2
29-Jan-18	Closed	Project A	2	2
30-Jan-18	Closed	Project A	4	6
30-Jan-18	New	Project B	1	1
31-Jan-18	Closed	Project A	6	12
31-Jan-18	Closed	Project M	1	3
06-Feb-18	Closed	Project A	7	19
07-Feb-18	Active	Project B	1	2
18-May-18	Closed	Project R	1

Is it possible, pls help me to do this.

[Anonymous · Accepted solution]

Hi @PattemManohar 

I checked your dataset. I think you were wrong at the line 10th. because the state is active and Project is Project B it means we run across it for first time it should be 1 for Running total by state & Project column.

Please create a new column like that

column = CALCULATE(SUM(Table1[CountState]);FILTER(Table1;Table1[State]=EARLIER(Table1[State])&&Table1[Project]=EARLIER(Table1[Project])&&Table1[MergedDate - Copy]<=EARLIER(Table1[MergedDate - Copy])))

[sprasad]

Can't we do this calculation in the query table by creating a custom column itself? I want to use other columns in the tquery table to display the details. So if everything is available in the single table then it is easy for me

[PattemManohar]

@Anonymous  It was based on the sample test data that was provided initially, but now you have different structure of data. So the same solution will not work for that. Anyway, I hope now you have solved that.

[sprasad]

I think PattemManohar  solution was based on the previous table.

Thanks

[Anonymous]

Hi @sprasad your date column is irregular. It should be have a sort logic Ascending or descending. I think we were unable to do that in this sort.

I suppose it would be descending sort if it's okey for you.

Column = CALCULATE(SUM(Table1[CountState]);FILTER(Table1;Table1[State]=EARLIER(Table1[State])&&Table1[Date]>=EARLIER(Table1[Date])))