Calculation for consecutive work days for employee

[babyjb1979]

Hello everyone,

I've been tasked with creating a report that tracks consecutive workdays. Within the employee transaction table, there are two key columns: [Employee ID] and [Work Date]. I've already added a new column that's working well, except it's not applied correctly when employees are grouped together.

In an attempt to refine the report further, I introduced another column. Unfortunately, this didn't yield the desired results.

I have submitted this question 3 times on the forum but haven't got any answer.  Your help would be greatly appreciated!

[ThxAlot]

Calculation for consecutive.pbix

With only one helper column,

[babyjb1979]

Work Date	Employee ID
3/11/2023	1
3/25/2023	1
3/27/2023	1
3/28/2023	1
3/29/2023	1
3/30/2023	1
3/31/2023	1
4/8/2023	1
4/22/2023	1
5/6/2023	1
5/20/2023	1
6/3/2023	1
6/17/2023	1
7/1/2023	1
7/15/2023	1
7/29/2023	1
8/12/2023	1
8/30/2023	1
8/31/2023	1
9/1/2023	1
9/5/2023	1
9/6/2023	2
9/7/2023	2
9/8/2023	2
9/9/2023	2
9/10/2023	2
9/11/2023	2
9/12/2023	2

[v-zhangti · Accepted solution]

Hi, @babyjb1979 

You can try the following methods.

Column:

Judge = 
DATEDIFF ( [Work Date] - 2,
    MAXX ( FILTER ( 'Table',[Employee ID]=EARLIER('Table'[Employee ID])&& [Work Date]= EARLIER ( 'Table'[Work Date] ) - 1 ), [Work Date] ),
    DAY
)

First day = IF([Judge]<>BLANK(),0,1)

Consecutive days = 
VAR last_1_date =
    CALCULATE ( MAX ( 'Table'[Work Date]),
        FILTER ( 'Table',
            [Work Date] <= EARLIER ( 'Table'[Work Date] )
            &&[Employee ID]=EARLIER('Table'[Employee ID])
            && [First day] = 1
        )
    )
VAR sum_ =
    CALCULATE ( COUNTROWS ( 'Table' ),
        FILTER ( 'Table',
            [Work Date] >= last_1_date
            &&[Employee ID]=EARLIER('Table'[Employee ID])
            &&[Work Date] <= EARLIER ( 'Table'[Work Date] )
        )
    )
RETURN
    IF ( [First day] = 1, 1, sum_ )

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.