Forum Discussion
Cumulative Value
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
6 Replies
Worksheet formula is enough
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...
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.
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 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.
