Calculated Column Cumulative Total Days by Employee - Resetting Counter Based on Gaps in Employment

[CaseyM]

Hello,

I am having a hard time with this calculation.  I can calculate the total cumulative days employed, but want to reset the counter based on gaps in employment that are greater then 60 days.  

Here is the sample data.  I want to have the results in the last column.  If "Employment" = "Gap in Employment", then reset the counter on the subsequent row for this calculated column.  

Here is the formula I use for the cumulative total.  How can I adjust this to reset the counter for days employed?

Thanks for your help,

Casey

[v-rzhou-msft · Accepted solution]

Hi @CaseyM ,

I think you need to calcualte the cumlative total for each ID in group of "Gap in Employment". I suggest you to add a group column in Power Query Editor firstly.

let _Count =
List.Count(
let 
_ID = [Employee ID],
_Date = [Previous Check Date]
in
Table.SelectRows(#"Sorted Rows",each _ID = [Employee ID] and  _Date>=[Previous Check Date] and [Employment Status] = "Gap in Employment")[Previous Check Date])
in
if [Employment Status] = "Gap in Employment" then _Count else _Count+1

Calculated column:

Column =
VAR _Cumulative_Total =
    CALCULATE (
        SUM ( URP30300[Days Since Previous Check] ),
        FILTER (
            ALLEXCEPT ( URP30300, URP30300[Employee ID], URP30300[Group] ),
            URP30300[Previous Check Date] <= EARLIER ( URP30300[Previous Check Date] )
        )
    )
VAR _Result =
    IF ( URP30300[Employment Status] <> "Gap in Employment", _Cumulative_Total )
RETURN
    _Result

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FreemanZ]

hi @CaseyM 

try like:

Column =

VAR _start =

MINX(

    FILTER(

        UPR,

        UPR[DaysCheck]<60

            &&UPR[Date]<=EARLIER(UPR[Date])

            &&UPR[ID]=EARLIER(UPR[ID]) 

    ),

    UPR[Date]

)

RETURN

SUMX(

    FILTER(

        UPR,

        UPR[Date]>=_start

           &&UPR[Date]<=EARLIER(UPR[Date])

    ),

   UPR[DaysCheck] 

)