Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CaseyM
Frequent Visitor

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.  

 

CaseyM_0-1680301666234.png

 

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

CaseyM_1-1680301739779.png

 

Thanks for your help,

 

Casey

 

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

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

vrzhoumsft_0-1681283748263.png

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.

vrzhoumsft_1-1681284138231.png

 

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.

 

View solution in original post

2 REPLIES 2
v-rzhou-msft
Community Support
Community Support

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

vrzhoumsft_0-1681283748263.png

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.

vrzhoumsft_1-1681284138231.png

 

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
Super User
Super User

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] 
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors