Forum Discussion

jaymccorp's avatar
jaymccorp
Frequent Visitor
7 years ago
Solved

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 p...
Need Help
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
7 years ago

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's avatar
jaymccorp
Frequent Visitor
7 years ago

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's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    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

      • jaymccorp's avatar
        jaymccorp
        Frequent Visitor
        7 years ago

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

         

        THANKS A LOT!!