Forum Discussion

RudyL's avatar
RudyL
Icon for Helper I rankHelper I
2 years ago
Solved

Absence streak

Hi, The request may look familiar but I can't figure it out with the examples in the community. So I put in a new one.  I have a table like the one below (first five columns). All employees are cro...
  • tamerj1's avatar
    tamerj1
    2 years ago

    Hi RudyL 
    You are right the ABSSTREAK calculated column. However I could not generate the other problem. Looks like the engine treats the blank as a zero anyway. I have a added a COALESCE statement as an additional layer to confirm the result.

     

RudyL's avatar
RudyL
Icon for Helper I rankHelper I
2 years ago

Hi tamerj1 ,

First of all i'm amazed, surprised, in awe for your quick response and effort in trying to explain me how this works. First take-away on my side: There's a lot to learn.

 

But... it doesn't work for my data. After looking at your code and matching our data I found a couple of things:

1. I think you missed some checks on having multiple employees. My data has 350 while your example only has one. So I think I need to change your ABSSTREAK to the one below where an extra check on EMPID is included  

ABSSTREAK =
IF (
    'Table'[ABSFLAG] = 1,
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[ABSDATE] <= EARLIER ( 'Table'[ABSDATE] )
            && 'Table'[EMPID] = EARLIER ( 'Table'[EMPID] )    
         ),
        'Table'[ABSSTARTFLAG]
    )
)
 
2. The change from (1) required another change in ABSDAYS.
ABSDAYS =
IF (
    MAX ( 'Table'[ABSSTARTFLAG] ) = 1,
    CALCULATE (
        SUM ( 'Table'[ABSFLAG] ),
        ALL ( 'Table'[ABSDATE] ),
        VALUES ( 'Table'[ABSSTREAK] )
       ,'Table'[EMPID]=MAX('Table'[EMPID])
    ),
    0
)
 
3. And another issue I couldn't resolve myself. When the first date in a dataset for an employee is an absence day (e.g. 1-1-2024 for a year overview), the ABSSTARTFLAG is not as it should be. It seems the offset -1 results in an error for that first date, and therefore for the complete first streak. 
 
It would be great if you would have a look at that issue (3).