Cumulative Value

[tony_tohme]

Hi There

I have this table:

Function	Date	Status	Count
A	6/1/2021	DONE	3
A	6/9/2021	NOT DONE	2
A	6/23/2021	DONE	1
A	8/11/2021	DONE	7
B	6/11/2021	DONE	7
B	7/22/2021	NOT DONE	6

How can I get a cumulative value column for each function based on month and for status = all and status = DONE, like this:

Function	Date	Status	Count	cumulative value all	cumulative value DONE
A	6/1/2021	DONE	3	3	3
A	6/9/2021	NOT DONE	2	5
A	6/23/2021	DONE	1	6	4
A	8/11/2021	DONE	7	13	11
B	6/11/2021	DONE	7	7	7
B	7/22/2021	NOT DONE	6	13

What I am trying to get is a graph line chart for function A showing the cumulative value all and cumulative value DONE  for June, and August and another graph same thing for function B.

Thank you very much

[Ashish_Mathur]

Hi,

Try this calculated column formulas

Cumulative count = CALCULATE(SUM(Data[Count]),FILTER(Data,Data[Function]=EARLIER(Data[Function])&&Data[Date]<=EARLIER(Data[Date])))

Cumulative count - Done = if(Data[Status]="Not Done",BLANK(),CALCULATE(SUM(Data[Count]),FILTER(Data,Data[Function]=EARLIER(Data[Function])&&Data[Status]="Done"&&Data[Date]<=EARLIER(Data[Date]))))

Hope this helps.

[tony_tohme]

The cumulative Done worked , but the Cumulative all is coming up with weird number not sure if they're true.

function	Date	status	Count		Cumulative all	Cumulative done	Date
A	4/1/2021	done	3		3	3	2021-04-01 0:00
A	5/1/2021	done	2		5	5	2021-05-01 0:00
A	6/1/2021	done	5		39	10	2021-06-01 0:00
A	6/1/2021	not done	1		46	17	2021-07-01 0:00
A	6/1/2021	not done	2		28	22	2021-08-01 0:00
A	7/1/2021	done	7		64	25	2021-09-01 0:00
A	7/1/2021	not done	3		126	29	2021-10-01 0:00
A	8/1/2021	done	5		144	33	2021-11-01 0:00
A	9/1/2021	not done	1		106	35	2021-12-01 0:00
A	9/1/2021	done	3		57		2022-01-01 0:00
A	10/1/2021	not done	5		124		2022-02-01 0:00
A	10/1/2021	done	4		73		2022-03-01 0:00
A	10/1/2021	not done	1		74		2022-04-01 0:00
A	11/1/2021	not done	1
A	11/1/2021	done	4
A	11/1/2021	not done	1
A	12/1/2021	not done	3
A	12/1/2021	done	2
A	1/1/2022	not done	4
A	2/1/2022	not done	4
A	2/1/2022	not done	1
A	3/1/2022	not done	11
A	4/1/2022	not done	1

[v-yanjiang-msft]

Hi @tony_tohme ,

I suspect the error is because @Ashish_Mathur 's formula is "Not Done", but your data is "not done", you should modify this in your formula.

Cumulative count - Done = if(Data[Status]="Not Done",BLANK(),CALCULATE(SUM(Data[Count]),FILTER(Data,Data[Function]=EARLIER(Data[Function])&&Data[Status]="Done"&&Data[Date]<=EARLIER(Data[Date]))))

---

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

As you can see in my screenshot, my formulas are working fine.  I am not sure of what you are doing.  Share the link from where i can download your PBI file with the formula already written there. 

[CNENFRNL]

Worksheet formula is enough

[tony_tohme]

Thank you, I tried your version and I got the same result as my reply above for the cumulative all, not sure if this is the expected result...