Absence streak

[RudyL]

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 crossjoined with (almost) all dates of two years. For each date per employee the absflag = 1 if the employee was away, 0 if the employee was available. 

I want to count streaks and streak lengths. Two measures seem to be useful.

- newmeasure1: when an absence streak starts i want to see the date the streak ends

- newmeasure2: when an absence streak starts i want to see the count of dates the streak lasts (not counting the dates not in the table)

So when an employee is available on a date, newmeasure1 is blank and newmeasure2 is 0. When an employee is absent, newmeasure1 is the last date of the current absence streak, but only when it's the date the streak starts. Dates after the first absent date in a an absence streak return blank for newmeasure1. 

The same goes for newmeasure2. The length of an absence streak is only greater than 0 for the startdate of a streak.

Is this a boring question or can someone help me 🙂

[tamerj1 · Accepted solution]

Hi @RudyL 
Please refer tao attached sample file with the proposed solution

[RudyL]

@tamerj1 can I add an extra request to the solution above. The result now is a list that calculates what I need per dept / employee / date. 

But suppose I want a more general clustered or stacked bar chart that shows per dept the total number of absence streaks that lasted less than 3 days and the total number of absence streaks that lasted 3 days or more. 

I noticed changing this context had some strange effects on your calculations. 

[RudyL]

Hi @tamerj1,

your last update with the coalesce did the trick. Forget about my third remark. I thought that absences that started on the first date of the data set were not correctly handled, but they are. It turns out I used filters per year and had to add the absence year as an extra partition to make it work. That would split absence streaks from one year to the other as two seperate ones. 

So thanks again !!! 

[tamerj1 · Accepted solution]

Hi @RudyL 
Please refer tao attached sample file with the proposed solution

[RudyL]

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]

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]

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 · Accepted solution]

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.

 

[v-yaningy-msft]

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]

Hi @v-yaningy-msft ,

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.

DEPT	EMPID	ABSDATE	ABSFLAG
DEPT_B	513019	02/01/2023	1
DEPT_B	513019	04/01/2023	1
DEPT_B	513019	05/01/2023	0
DEPT_B	513019	09/01/2023	1
DEPT_B	513019	11/01/2023	0
DEPT_B	513019	12/01/2023	0
DEPT_B	513019	16/01/2023	0
DEPT_A	513020	18/01/2023	1
DEPT_B	513019	18/01/2023	1
DEPT_A	513020	19/01/2023	0
DEPT_B	513019	19/01/2023	0
DEPT_B	513019	23/01/2023	0
DEPT_A	513020	23/01/2023	1
DEPT_B	513019	25/01/2023	1
DEPT_B	513019	26/01/2023	1
DEPT_B	513019	27/01/2023	1
DEPT_A	513020	27/01/2023	0
DEPT_B	513019	30/01/2023	1
DEPT_B	513019	31/01/2023	1
DEPT_B	513019	01/02/2023	1
DEPT_B	513019	02/02/2023	1
DEPT_A	513020	02/02/2023	1
DEPT_A	513020	03/02/2023	1
DEPT_B	513019	03/02/2023	1
DEPT_B	513019	06/02/2023	0
DEPT_B	513019	08/02/2023	0