Forum Discussion

CaseyM's avatar
CaseyM
Frequent Visitor
3 years ago
Solved

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

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

 

calculated column
Cumulative Total
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

     

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    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] 
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.