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).  
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi, RudyL 

Thanks for tamerj1 reply. Can I ask if you have currently resolved the third issue? I used tamerj1's pbix file and then added DEPT_B with an ABSDATE of 1/1/2023, and the line corresponding to ABSSTARTFLAG seems to be the expected behavior. I don't understand what's wrong and if you can describe the effect you're looking for.

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

  • RudyL's avatar
    RudyL
    Helper I
    2 years ago

    Hi Anonymous ,

    Thanks for your curiosity. But try the table below in which I inserted a second employee (513020) from a different department in the same data that tamerj1 used (just the 513019). When you take this dataset you will see that the calculations will be completely different, and wrong.

     

    DEPTEMPIDABSDATEABSFLAG
    DEPT_B51301902/01/20231
    DEPT_B51301904/01/20231
    DEPT_B51301905/01/20230
    DEPT_B51301909/01/20231
    DEPT_B51301911/01/20230
    DEPT_B51301912/01/20230
    DEPT_B51301916/01/20230
    DEPT_A51302018/01/20231
    DEPT_B51301918/01/20231
    DEPT_A51302019/01/20230
    DEPT_B51301919/01/20230
    DEPT_B51301923/01/20230
    DEPT_A51302023/01/20231
    DEPT_B51301925/01/20231
    DEPT_B51301926/01/20231
    DEPT_B51301927/01/20231
    DEPT_A51302027/01/20230
    DEPT_B51301930/01/20231
    DEPT_B51301931/01/20231
    DEPT_B51301901/02/20231
    DEPT_B51301902/02/20231
    DEPT_A51302002/02/20231
    DEPT_A51302003/02/20231
    DEPT_B51301903/02/20231
    DEPT_B51301906/02/20230
    DEPT_B51301908/02/20230