topic Re: Calculated Column Cumulative Total Days by Employee - Resetting Counter Based on Gaps in Employment in DAX Commands and Tips

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

CaseyM — Fri, 31 Mar 2023 22:29:13 GMT

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

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

FreemanZ — Sat, 01 Apr 2023 00:03:27 GMT

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]

)

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

Anonymous — Wed, 12 Apr 2023 07:22:50 GMT

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.