Forum Discussion

babyjb1979's avatar
babyjb1979
Icon for Helper I rankHelper I
3 years ago
Solved

Calculation for consecutive work days for employee

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!

 

Need Help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    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.

3 Replies

  • babyjb1979's avatar
    babyjb1979
    Icon for Helper I rankHelper I
    3 years ago
    Work DateEmployee ID
    3/11/20231
    3/25/20231
    3/27/20231
    3/28/20231
    3/29/20231
    3/30/20231
    3/31/20231
    4/8/20231
    4/22/20231
    5/6/20231
    5/20/20231
    6/3/20231
    6/17/20231
    7/1/20231
    7/15/20231
    7/29/20231
    8/12/20231
    8/30/20231
    8/31/20231
    9/1/20231
    9/5/20231
    9/6/20232
    9/7/20232
    9/8/20232
    9/9/20232
    9/10/20232
    9/11/20232
    9/12/20232
    • v-zhangti's avatar
      v-zhangti
      Icon for Community Support rankCommunity Support
      3 years ago

      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.