Cumulative sum by date by condition

[Anonymous]

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?  

[v-lili6-msft · Accepted solution]

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

[Anonymous]

Hi Lin, 

What is the difference between result 1 and result 2

Regards,

Sai

[Anonymous]

Hi Lin, you are right about the error in my cumulative sum. Your formula 'result 2' works perfectly, thank you very much! 

[Stachu]

the code you posted should work fine as long as you put the Condition in the visual - e.g. in rows