topic Re: Calculation for consecutive work days for employee in DAX Commands and Tips

Calculation for consecutive work days for employee

babyjb1979 — Wed, 06 Sep 2023 17:24:55 GMT

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!

Re: Calculation for consecutive work days for employee

babyjb1979 — Wed, 06 Sep 2023 17:54:33 GMT

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

Re: Calculation for consecutive work days for employee

v-zhangti — Fri, 08 Sep 2023 01:52:45 GMT

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.

Re: Calculation for consecutive work days for employee

ThxAlot — Fri, 08 Sep 2023 23:58:23 GMT

Calculation for consecutive.pbix

With only one helper column,