Find consecutive dates in table

[rileybloke]

Hi,

I have a table of data that contains employee attendance records.

The table consists of employee name, date, attended mins, and absence mins

I need to find where an employee as been off sick for 15 consecutive days, and exclude the absence mins of these days from the total sum of absences.

Any help with the DAX would be greatly appreciated.
Thanks
Phil

 

[scottsen]

I suggest you put some effort into you calendar table. Get everything date related in there you can, including info about the holidays. If you can end up with a notion in the Calendar table of "this day, I expect people to be at work..." that will help our problems

Imagine you have a column in your calendar table "ValidWorkday" that is a 1 if missing that day would require using a sick day. Then, we could invent a "WorkdayNumber" column like:

=IF (Calendar[ValidWorkday],
CALCULATE(COUNTROWS(Calendar), FILTER(Calendar, Calendar[Date] <= EARLIER(Calendar[Date]) && Calendar[ValidWorkday] = 1))
)

Then in the Attendance table you could do something like...

=CALCULATE(COUNTROWS(Attendance),
         FILTER(Calendar, Calendar[WorkDayNumber] > RELATED(Calendar[WorkDayNumber]) - 15  &&
                                            Calendar[WorkDayNumber] <= RELATED(Calendar[WorkDayNumber])),
         Attendance[Sick Day] = "Y",
         FILTER(Attendance,Attendance[Clock No]=EARLIER( Attendance[Clock No]))
    )

Which is the "Of the last 15 working days, how many did you miss?" If it's 15, then it's probably interesting.

(though, it is not super clear to me what we do if they missed 20 days in a row)

 

[ChrisWebb]

I know you want to do this in DAX, but if you can use Power Query I suspect it would be much easier to find these ranges when you are loading the data. You could then create another column in your table that identified these ranges, making the DAX required simpler.

This problem is similar to the one I blogged about here: Aggregating By Local Groups In Power Query | Chris Webb's BI Blog. The last chapter of my Power Query book (Power Query Book Published! | Chris Webb's BI Blog) also has a similar worked example.

HTH,

Chris

 

[scottsen]

Okay, that blog post blew my mind, Chris! Power Query is kind of amazeballs sometimes.

 

[rileybloke]

Hi Scott, thanks for the heads up on the calendar, I have taken your advice and now may calendar table is a little more useful. Your DAX to count the consecutive sick days is working thanks.
The rule is to exclude the sick attended for long term sick. Long term sick = any employee that is off sick for more than 15 consecutive days inclusive, therefore I'm now trying to sum the "Sick Attended" but exclude the "Sick Attended" where the count is between 1 and 15 and exceeds 15, failing miserably any ideas? thanks again.
I updated the workbook.

Thanks Chris, Ive never considered Powerview will have a dig around

 

[rileybloke]

Juts been told that we should only be considering consecutive sick days when the sick day type = 1 or 3 !
Yikes! just to make it more complicated

 

[scottsen]

Model looks pretty good to me.

Does this give what you want? (ignoring that sick day type )
=CALCULATE([Absence Hrs], Attendance[Consecutive Sick Count] < 15)

 

[rileybloke]

oh yes! simples! thank you very much

 

[Abhay Gadiya]

I had similar issue where i did not use PowerPivot, have a look at this video - https://www.youtube.com/watch?v=BKa8Bbdhq7I

 

[Abhay Gadiya]

I have little reverse problem. I have a uploaded a video on youtube - https://www.youtube.com/watch?v=BKa8Bbdhq7I
Can you please help me in reducing the steps i need to perform or creating any DAX patterns which i can use in PowerPivot.