Cumulative sum by category, date and with a reset filter?

[Anonymous]

Greetings everyone!

I have a table with those features

Machine	Production Hrs	Fault	Date
A	8	0	1/1/20
A	5	0	2/1/20
A	3	0	10/1/20
B	4	0	15/1/20
A	0	1	18/1/20
B	8	0	23/1/20
A	4	0	3/2/20
B	8	0	6/2/20
B	0	1	10/2/2020

And i would a new cumulative column which sum the hours of production by date and category, and  reset when Fault column is 1, as follows:

Machine	Production Hrs	Fault	Date	Cumulative Hrs
A	8	0	1/1/20	8
A	5	0	2/1/20	13
A	3	0	10/1/20	16
B	4	0	15/1/20	4
A	0	1	18/1/20	16
B	8	0	23/1/20	12
A	4	0	3/2/20	4
B	8	0	6/2/20	20
B	0	1	10/2/2020	20

I found how to cumulate by date and categories, but not the case of reset with a value, I found only with period!

Any ideas?

[d_gosbell]

So you could create a calculated column with an expression like the following

Cummulative Hrs = 
// get the date for the current row
VAR maxDate = 'Table'[Date]
// get the minimum date for the table
VAR minDate = MINX(ALL('Table'[Date]), 'Table'[Date])
// get the current machine
VAR machine = 'Table'[Machine]
// find the previous fault date
VAR minFaultDate = CALCULATE(MAX('Table'[Date]),ALL('Table'), 'Table'[Date] < maxDate , 'Table'[Fault] = 1, 'Table'[Machine] = machine )
// either use the previous fault date or the min date in the table as the starting point for the cummulative sum
var fromDate = COALESCE(minFaultDate, minDate)
// sum the hours between the fromDate and maxDate for the current machine
RETURN CALCULATE(sum('Table'[Production Hrs]), ALL('Table'), 'Table'[Date] >= fromDate, 'Table'[Date] <= maxDate, 'Table'[Machine] = machine)

Note that this calc column makes use of CALCULATE and I usually try and avoid that in calc columns as it causes a context transition and can cause issues if you try and create more than one calculated column that uses CALCULATE.

So you could do a similar calculation as a measure (which is calculated at run time and not stored like a column)

Cummulative Hrs (Measure) = 
// get the date for the current row
VAR maxDate = MAX('Table'[Date])
// get the minimum date for the table
VAR minDate = CALCULATE(MIN('Table'[Date]), all('Table'[Date]))
// get the current machine
VAR machine = SELECTEDVALUE('Table'[Machine])
// find the previous fault date
VAR minFaultDate = CALCULATE(MAX('Table'[Date]),ALL('Table'), 'Table'[Date] < maxDate , 'Table'[Fault] = 1, 'Table'[Machine] = machine )
// either use the previous fault date or the min date in the table as the starting point for the cummulative sum
var fromDate = COALESCE(minFaultDate, minDate)
// sum the hours between the fromDate and maxDate for the current machine
RETURN CALCULATE(sum('Table'[Production Hrs]), ALL('Table'), 'Table'[Date] >= fromDate, 'Table'[Date] <= maxDate, 'Table'[Machine] = machine)