Forum Discussion
Running total with date reset
I am trying to do a fatigue calculation that does a running total of hours worked, and days in a row, but it resets when the person does not come in for work for one day. I have looked at the past posts, and can get the running total, but I do not know how to get the sum to restart when the person does not come in. This is what I want it do to:
| Date | Employee Number | Time - Hours | Fatigue Hrs - running total | Days in row |
| 6/19/18 | 1002 | 7.42 | 7.42 | 1 |
| 6/20/18 | 1002 | 7.92 | 15.34 | 2 |
| 6/21/18 | 1002 | 1.18 | 16.52 | 3 |
| 6/23/18 | 1002 | 1.73 | 1.73 | 1 |
| 6/24/18 | 1002 | 0.97 | 2.7 | 2 |
| 6/25/18 | 1002 | 1.27 | 3.97 | 3 |
| 7/2/18 | 1002 | 5.08 | 5.08 | 1 |
| 7/10/18 | 1002 | 0.65 | 0.65 | 1 |
| 7/11/18 | 1002 | 3.5 | 4.15 | 2 |
| 7/13/18 | 1002 | 1.65 | 1.65 | 1 |
| 6/18/18 | 1004 | 9.77 | 9.77 | 1 |
| 6/19/18 | 1004 | 9.7 | 19.47 | 2 |
| 6/20/18 | 1004 | 11.67 | 31.14 | 3 |
| 6/21/18 | 1004 | 9.6 | 40.74 | 4 |
| 6/22/18 | 1004 | 8.12 | 48.86 | 5 |
| 6/23/18 | 1004 | 1 | 49.86 | 6 |
| 6/25/18 | 1004 | 9.7 | 9.7 | 1 |
| 6/26/18 | 1004 | 9.83 | 19.53 | 2 |
| 6/27/18 | 1004 | 9.65 | 29.18 | 3 |
| 6/28/18 | 1004 | 9.65 | 38.83 | 4 |
| 7/2/18 | 1004 | 9.83 | 9.83 | 1 |
| 7/3/18 | 1004 | 9.72 | 19.55 | 2 |
| 7/5/18 | 1004 | 9.63 | 9.63 | 1 |
| 7/6/18 | 1004 | 8.7 | 18.33 | 2 |
| 7/7/18 | 1004 | 1 | 19.33 | 3 |
| 7/10/18 | 1004 | 9.78 | 9.78 | 1 |
| 7/11/18 | 1004 | 9.77 | 19.55 | 2 |
| 7/12/18 | 1004 | 9.6 | 29.15 | 3 |
| 7/13/18 | 1004 | 8.48 | 37.63 | 4 |
| 7/14/18 | 1004 | 2 | 39.63 | 5 |
Thanks in advance for your help. This is my first post, but I have read a lot of different posts on MANY different questions. It has been very helpful. Great forum!
6 Replies
If you already have a days in a row column
you can use this calculated column to get the running total that resets
Running Total = VAR mydays = [Date] - [Days in row] + 1 RETURN SUMX ( FILTER ( Table1, [Employee Number] = EARLIER ( [Employee Number] ) && [Date] <= EARLIER ( [Date] ) && [Date] >= mydays ), [Time - Hours] )Thanks, but I do not have days in a row either. I am tying to create the two last columns. I have been able to get a running sum for hours, but I need it to reset when the person does not come to work for one day. The count and the running total will then reset and start again once the person takes a day off.
Thanks for the help!
In that case, to get the Days in a Row column we can first add a supporting column which determines when running total is reset
FindCounterReset = VAR PreviousDate = MINX ( TOPN ( 1, FILTER ( Table1, [Employee Number] = EARLIER ( [Employee Number] ) && [Date] < EARLIER ( [Date] ) ), [Date], DESC ), [Date] ) RETURN IF ( [Date] <> PreviousDate + 1, "Counter reset" )Now we can add the Days in Row Column as follows
Days in a Row = VAR counterstart = MINX ( TOPN ( 1, FILTER ( Table1, [Employee Number] = EARLIER ( [Employee Number] ) && [Date] <= EARLIER ( [Date] ) && [FindCounterReset] = "Counter reset" ), [Date], DESC ), [Date] ) VAR counterend_ = MINX ( TOPN ( 1, FILTER ( Table1, [Employee Number] = EARLIER ( [Employee Number] ) && [Date] > EARLIER ( [Date] ) && [FindCounterReset] = "Counter reset" ), [Date], ASC ), [Date] ) VAR counterend = IF ( counterend_ = BLANK (), DATE ( 3000, 1, 1 ), counterend_ ) RETURN RANKX ( FILTER ( Table1, [Employee Number] = EARLIER ( [Employee Number] ) && [Date] >= counterstart && [Date] < counterend ), [Date], , ASC, DENSE )Now you can use the Column for running total in the previous post
