Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
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.
hi @CaseyM
try like:
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |