Running total with date reset

[jaymccorp]

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!

[Zubair_Muhammad]

@jaymccorp

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]
    )

[jaymccorp]

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!

[Zubair_Muhammad]

@jaymccorp

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

[Zubair_Muhammad · Accepted solution]

@jaymccorp

Please see the attached file with your sample data and above calculated columns

[jaymccorp]

That is AMAZING!  It works!!  Now I have to study it for a couple of hours to understand why.

THANKS A LOT!!

[Shelley]

@Zubair_Muhammad I am trying to do something similar, but want the cumulative total per contract to reset at the end of the contract.

I have contract data like this:

CONTRACT INPUTS
BPID	Customer Name (BPID)	contract_num	SAP Contract Start	SAP Contract End	Contract Cap
399	ABC COMPANY (399)	8003656409	2/1/2018	1/31/2019	$    8,000.00
399	ABC COMPANY (399)	8004081579	2/1/2019	1/31/2020	$  10,000.00
399	ABC COMPANY (399)	8004575589	2/1/2020	1/31/2021	$    9,000.00

And then I have transaction data. I want to plot the contract cap, which I figured out how to do, AND the cumulative data.

DATA INPUTS	DATA INPUTS	DESIRED RESULT
Order Submit Date	List_Price	Cumulative Usage	Notes
2/1/2018		0	Usage begins at 0 on 2/1/18, which is start of the contract above
2/21/2018	$2,592	$2,592
8/23/2018	$1,542	$4,134
10/12/2018	$1,709	$5,843
10/17/2018	$3,893	$9,736
12/4/2018	$513	$10,249
2/1/2019		$0	Usage begins at 0 again on 2/1/19, with a new contract
2/25/2019	$2,940	$2,940
5/14/2019	$1,186	$4,126
7/18/2019	$0	$4,126
8/23/2019	$323	$4,449
9/2/2019	$545	$4,994
9/24/2019	$2,636	$7,630
2/1/2020		$0	Usage begins again at 0 on 2/1/20
2/7/2020	$1,737	$1,737
2/19/2020	$128	$1,865
2/20/2020	$128	$1,993

The repair entitlements (blue line) plot correctly, but the cumulative value (red line) keeps accumulating instead of resetting to 0 at the start of a new contract.

Here are my formulas:

Repair Entitlement Amount =

CALCULATE(SUM('Entitlements'[Repair Cap]),

FILTER('QBContract','QBContract'[SAP Contract Start] <= MAX('RA_Daily_Calendar'[Date]) &&

'QBContract'[SAP Contract End] >= MAX('RA_Daily_Calendar'[Date])))

I know this will not work, but does accumulate:

List_Price running total in Fiscal_YearMonth =

CALCULATE(

    SUM('RepairTransaction'[List_Price]),

    FILTER(

        ALLSELECTED('RA_Daily_Calendar'[CalendarYear-Mo]),

        ISONORAFTER('RA_Daily_Calendar'[CalendarYear-Mo], MAX('RA_Daily_Calendar'[CalendarYear-Mo]), DESC)))

For this one I was trying to use the contract dates, but it does not accumulate, it only shows points in time for each repair (orange dots below).

Repair Amount List Price =

CALCULATE(SUM('RepairTransaction'[List_Price]),

FILTER(ALL('RepairTransaction'[Order_Submit_Date]), 'RepairTransaction'[Order_Submit_Date] >= MAX('QBContract'[SAP Contract Start]) &&

'RepairTransaction'[Order_Submit_Date] <= MAX('QBContract'[SAP Contract End])))

Any help is appreciated. Thanks!