Forum Discussion

RudyL's avatar
RudyL
Helper 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
Helper 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).  
tamerj1's avatar
tamerj1
Community Champion
2 years ago

Hi RudyL 

Looks like somehow I missed your reply. 
ABSENDDATE and ABSDAYS are measures. That means in the table visual the EMPID is already available in the fillter context as part of the visual. 

  • RudyL's avatar
    RudyL
    Helper I
    2 years ago

    Hi tamerj1 , please see my example data that i gave in reply to another contributor. With this sample data your solution doesn't seem to work.

    • tamerj1's avatar
      tamerj1
      Community Champion
      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.