Forum Discussion
Cumulative sum by date by condition
Hi,
I have a table with payments at different dates, associated with different conditions :
| Date | Sum | Condition |
| 01/01/2018 | 5 | A |
| 01/01/2018 | 20 | B |
| 02/01/2018 | 10 | B |
| 02/01/2018 | 20 | B |
| 03/01/2018 | 10 | A |
| 03/01/2018 | 20 | A |
| 04/01/2018 | 15 | B |
I would like to calculate the cumulative sum by date, by condition, such as :
| Date | Sum | Condition | Cumulative Sum |
| 01/01/2018 | 5 | A | 5 |
| 01/01/2018 | 20 | B | 20 |
| 02/01/2018 | 10 | B | 30 |
| 02/01/2018 | 20 | B | 50 |
| 03/01/2018 | 10 | A | 15 |
| 03/01/2018 | 20 | A | 25 |
| 04/01/2018 | 15 | B | 65 |
I found this in a similar post :
Cumulative Sum=
CALCULATE (
SUM (Table[Sum]), FILTER (ALL (Table[Date] ),Table[Date] <= MAX ( Table[Date])))
But I don't see how to adapt to my case, so it takes in account the condition.
Any ideas?
hi, Anonymous
After my research, you could do these as below:
First is there some errors in your expected output “Cumulative Sum”
for 02/01/2018 condition B, why one row is 30 and another is 50
but 03/01/2018 condition A, why one row is 15 and another is 25
please check the expected output “Cumulative Sum”
and I have provided two formula for you to refer to:
result 1 = CALCULATE ( SUM ( 'Table'[Sum] ), FILTER ( ALL ( 'Table' ), 'Table'[Condition] = EARLIER ( 'Table'[Condition] ) && 'Table'[Date] < EARLIER ( 'Table'[Date] ) ) ) + 'Table'[Sum]result 2 = CALCULATE ( SUM ( 'Table'[Sum] ), FILTER ( ALL ( 'Table' ), 'Table'[Condition] = EARLIER ( 'Table'[Condition] ) && 'Table'[Date] <= EARLIER ( 'Table'[Date] ) ) )Result:
here is pbix, please try it.
https://www.dropbox.com/s/wqt1qi8hphipnmk/Cumulative%20sum%20by%20date%20by%20condition.pbix?dl=0
Best Regards,
Lin
4 Replies
v-lili6-msftCommunity Support
hi, Anonymous
After my research, you could do these as below:
First is there some errors in your expected output “Cumulative Sum”
for 02/01/2018 condition B, why one row is 30 and another is 50
but 03/01/2018 condition A, why one row is 15 and another is 25
please check the expected output “Cumulative Sum”
and I have provided two formula for you to refer to:
result 1 = CALCULATE ( SUM ( 'Table'[Sum] ), FILTER ( ALL ( 'Table' ), 'Table'[Condition] = EARLIER ( 'Table'[Condition] ) && 'Table'[Date] < EARLIER ( 'Table'[Date] ) ) ) + 'Table'[Sum]result 2 = CALCULATE ( SUM ( 'Table'[Sum] ), FILTER ( ALL ( 'Table' ), 'Table'[Condition] = EARLIER ( 'Table'[Condition] ) && 'Table'[Date] <= EARLIER ( 'Table'[Date] ) ) )Result:
here is pbix, please try it.
https://www.dropbox.com/s/wqt1qi8hphipnmk/Cumulative%20sum%20by%20date%20by%20condition.pbix?dl=0
Best Regards,
Lin
- Stachu
Community Champion
the code you posted should work fine as long as you put the Condition in the visual - e.g. in rows
