Displaying (Flagging) employees which have attended on a certain number of consecutive days

[Jamesfindog]

Hi All,

Looking to flag when an employee has worked at least 3 consecutive days.

Data example:

Employee	Day
A	1
A	2
A	3
A	4
A	5
A	6
B	1
B	2
C	1
A	7
A	8
A	9
B	10
B	11
B	12
A	12
A	13
A	14

So, employee A should be flagged 3 times, and B once, with C never.

I'd want to be able to display which employees have worked 3 or more consecutive days, potentially with the days they have worked. (Day is actually a date field, I simplified it to numbers for this help message).

What do you think is the best way to display this?

Cheers!

[v-echaithra]

Hi @Jamesfindog ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-echaithra]

Hi @Jamesfindog ,

We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

Best Regards,
Chaithra E.

[v-echaithra]

Hi @Jamesfindog ,

Thank you @johnt75 , @AmiraBedh , @srlabhe  for your inputs.

I hope the information provided is helpful. I wanted to check whether you were able to resolve the issue with the provided solutions. Please let us know if you need any further assistance.

Thank you.

[johnt75]

There's a flaw in your test data. As A's attendance from 6 continues at 7 there are only 2 stretches where they attend consecutively for more than 3 days.

You can create a measure like

VAR AllDate = ALL('Date'[Date])
VAR DatesAttended = VALUES('Table'[Date])
VAR DatesNotAttended = EXCEPT(
    AllDate,
    DatesAttended
)
VAR TableForGrouping = GENERATE(
    DatesAttended,
    VAR RefDate = 'Table'[Date]
    VAR PrevNotAttended = MAXX(
        FILTER(
            DatesNotAttended,
            'Date'[Date] <= RefDate
        ),
        'Date'[Date]
    )
    VAR NumDays = DATEDIFF(
        PrevNotAttended,
        RefDate,
        DAY
    )
    VAR Result = ROW(
        "@num days", NumDays,
        "@prev not attended", PrevNotAttended
    )
    RETURN
        Result
)
VAR AttendedStretches = GROUPBY(
    TableForGrouping,
    [@prev not attended],
    "@longest attendance",
    MAXX(
        CURRENTGROUP(),
        [@num days]
    )
)
VAR Result = COUNTROWS(FILTER(
    AttendedStretches,
    [@longest attendance] >= 3
))
RETURN
    Result

This works out how for each date how many days continuous attendance has happened, as well as storing the previous date on which the person did not attend. It then groups all the dates with the same non-attendance date and gets the max of the attendance. It finally counts how many groups have 3 or more consecutive attendance dates.

See attached PBIX for reference.

[AmiraBedh · Accepted solution]

Hello !

Thank you for posting Microsoft Fabric community.

You can take the employee distinct dates in the current filters and rank them and compute GrpKey =o each consecutive run shares a key then group by that key to get each run length. Pick the longest run length and do QUOTIENT(longest, 3).

Flag (longest run / 3) =
VAR Emp =
    SELECTEDVALUE ( 'Attendance'[Employee] )
VAR DatesForEmp =
    CALCULATETABLE ( DISTINCT ( 'Attendance'[Day] ), 'Attendance'[Employee] = Emp )
VAR Ranked =
    ADDCOLUMNS (
        DatesForEmp,
        "DaySerial", INT ( 'Attendance'[Day] ),
        "Idx", RANKX ( DatesForEmp, 'Attendance'[Day], , ASC, DENSE )
    )
VAR WithGrp =
    ADDCOLUMNS ( Ranked, "GrpKey", [DaySerial] - [Idx] )
VAR Runs =
    GROUPBY ( WithGrp, [GrpKey], "Len", COUNTX ( CURRENTGROUP (), 1 ) )
VAR LongestLen =
    MAXX ( Runs, [Len] )
RETURN
    QUOTIENT ( LongestLen, 3 )

[srlabhe]

Do you mean even Employee A worked through Day 1 - Day 6 it should be flagged only once or twice as he worked six days which are twice of 3 consecutive days ?

[Jamesfindog]

Hi mate, once.

As this is a single consecutive period, just happens to be over 3 days.

Hope this makes sense.

[srlabhe]

Create a column as below 

then take differnce beween this and your date column.

Then filter the difference for 1.

then take sum(difference) , here it should sum only consecutive days and where thsoe are 3 you can flag accordingly

Previous Row Value =
CALCULATE(
MAX( 'Values'[Value] ), // Replace 'Values'[Value] with your column
TOPN(
1,
FILTER(
ALLSELECTED( 'Values' ), // Replace 'Values' with your table
'Values'[Value Date] < MAX( 'Values'[Value Date] ) // Replace 'Values'[Value Date] with your date column
),
'Values'[Value Date], // Replace 'Values'[Value Date] with your date column
DESC
)
)

Noe for cases like employee A you have to tweak the logic saying if its >3 then count it once.