Cumulative sum group by 2 factors

[Anonymous]

Hi,

This is my example table which is named 'OP 2018'.

SBU	Date	Daily Target_OP	Cumulative Daily Target_OP
A	2018-09-01	10	10
A	2018-09-02	10	20
A	2018-09-03	10	30
A	2018-09-04	10	40
A	2018-09-05	10	50
A	2018-10-23	20	20
A	2018-10-24	20	40
A	2018-10-25	20	60
A	2018-10-26	20	80
A	2018-10-27	20	100
B	2018-09-01	15	15
B	2018-09-02	15	30
B	2018-09-03	15	45
B	2018-09-04	15	60
B	2018-09-05	15	75
B	2018-10-23	21	21
B	2018-10-24	21	42
B	2018-10-25	21	63
B	2018-10-26	21	84
B	2018-10-27	21	105
C	2018-09-01	17	17
C	2018-09-02	17	34
C	2018-09-03	17	51
C	2018-09-04	17	68
C	2018-09-05	17	85
C	2018-10-23	12	12
C	2018-10-24	12	24
C	2018-10-25	12	36
C	2018-10-26	12	48
C	2018-10-27	12	60
D	2018-09-01	11	11
D	2018-09-02	11	22
D	2018-09-03	11	33
D	2018-09-04	11	44
D	2018-09-05	11	55
D	2018-10-23	8	8
D	2018-10-24	8	16
D	2018-10-25	8	24
D	2018-10-26	8	32
D	2018-10-27	8	40

I want to have a result of 'Cumulative Daily Target_OP' number so I input below,

Cumulative Daily Target_OP = IF(ISBLANK(if('OP 2018'[Date]<=today(),CALCULATE (
    SUM ( 'OP 2018'[Daily Target_OP] ),
    ALLEXCEPT('OP 2018','OP 2018'[SBU]),
    'OP 2018'[Date] <= EARLIER ( 'OP 2018'[Date] )
))),0,if('OP 2018'[Date]<=today(),CALCULATE (
    SUM ( 'OP 2018'[Daily Target_OP] ),
    ALLEXCEPT('OP 2018','OP 2018'[SBU]),
    'OP 2018'[Date] <= EARLIER ( 'OP 2018'[Date] )
)))

But it doesn't work.. Maybe I've omitted for grouping by month but I cannot make it.

Please help me to find out the correct one.

[PattemManohar · Accepted solution]

@Anonymous Please try this as a "New Column"

CummTotal = CALCULATE(SUM(Test40CummTotal[Daily Target_OP]),
 FILTER(Test40CummTotal,Test40CummTotal[SBU]=EARLIER(Test40CummTotal[SBU]) 
 && Test40CummTotal[Date] <= EARLIER(Test40CummTotal[Date]) 
 && FORMAT(Test40CummTotal[Date],"YYYYMM") = FORMAT(EARLIER(Test40CummTotal[Date]),"YYYYMM")
 )
 )

 

[Anonymous]

@PattemManohar 

Hi tried your solution, didn't work for me.  I gave 2 regions but for 1 only it worked.

[Anonymous]

Thank you very much! It  really works 🙂