Forum Discussion

Mafesa_Sasol's avatar
Mafesa_Sasol
Frequent Visitor
3 years ago
Solved

Running Calculated Column Totals with reset

Hi all, I am trying to find a running total for each unique ID value[Personnel Number] which resets after a new consecutive row for that employee which is not 1. As you can see on the column, Consecutive Employee Check. The running total contunes through and does not reset after a new set of 1(ones) in the Days between Standby column. This is my DAX formula:

Consecutive Employee Check =

VAR CurrentStandbyDays = LOOKUPVALUE(Standby[Standby Days], Standby[Index], Standby[Index])

VAR ConsecStandbyDays =

IF(

LOOKUPVALUE(Standby[Personnel Number], Standby[Index], Standby[Index]) = LOOKUPVALUE(Standby[Personnel Number], Standby[Index], Standby[Index]+1)

&& LOOKUPVALUE(Standby[Days Between Standby], Standby[Index], Standby[Index]) < 7,


CALCULATE (
    SUM ( 'Standby'[Days Between Standby] ),

FILTER ( ALLEXCEPT(Standby, 'Standby'[Personnel Number]), LOOKUPVALUE(Standby[Days Between Standby], Standby[Index], Standby[Index]) = 1 && 'Standby'[Index] <= EARLIER ('Standby'[Index]))

)
,

CurrentStandbyDays
)


Return ConsecStandbyDays






Any help would be highly appreciated.