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]

Wow. I love this problem... cuz ****, that sounds hard I need to catch a flight, no way the magic comes to me before then, but I'll be thinking about it...

 

[scottsen]

I'm back in Seattle now, and while the answer didn't magically hit me... I figure we might as well at least do the "easy" part? A measure which is "of the past 15 days, how many of them were sick days?".

How do you define "off sick"? AttendedMins = 0 and AbsenceMins > 0 ?

You have a sample workbook I can play with? (if so, use google drive, one drive, or dropbox to get it to me?)

 

[rileybloke]

Hi Scott,
Sorry I didn't get the email notification that the thread was updated.
Thanks for the reply, there is contracted mins for the day, and sick attended mins for the day. If the employee's total sick = sick attended, then they were off sick for a full day. Then we must find 15 full consecutive days sick, and deduct the total sick mins of the 15 days or more from the total sick mins.
I have uploaded the workbook here called Attendance Reporting.xlsx

https://onedrive.live.com/?cid=081B7EB8AB14F5C8&id=81B7EB8AB14F5C8%21105

Thanks

 

[scottsen]

I'll take a look in the next few days. Client work is keeping me a bit busy :|

I'm afraid to ask, but what if there was a holiday while the person was sick? 7 days of sick days, 1 day of holiday, 7-8 more days of sick...

They wouldn't have been "charged" a sick day on a holiday typically?

 

[rileybloke]

Thanks, no probs. Excellent question

Any booked holidays that end up as sick will be changed to sick so there would be consecutive sick days. But...

Statutory holidays (national holidays) are different to booked holidays. These holidays do show up as a Holiday Day Type but dont have any recorded mins in the "Holiday Attended" nor will they have any mins in the "Sick Attended" column. I suppose we now need consider sick days + holiday days that are consecutive for 15 days or more.

I spoke to the guys today, and they need to see both sick measures.
All Sick
All Sick excluding long term sick

Thanks again.

 

[scottsen]

Okay, let's start somewhere concrete, even if it's wrong

Let's say you have a calculated column on your Attendance table:

=<br><span class="Keyword" style="color:#0070FF">CALCULATE</span><span class="Parenthesis" style="color:#D0D0D0"> (</span><br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">COUNTROWS</span><span class="Parenthesis" style="color:#D0D0D0"> (</span> Attendance <span class="Parenthesis" style="color:#D0D0D0">)</span>,<br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">FILTER</span><span class="Parenthesis" style="color:#D0D0D0"> (</span><br><span class="indent8">        </span>Attendance,<br><span class="indent8">        </span>Attendance[Dated]<br><span class="indent8">        </span><span class="indent4">    </span>> <span class="Keyword" style="color:#0070FF">EARLIER</span><span class="Parenthesis" style="color:#D0D0D0"> (</span> Attendance[Dated] <span class="Parenthesis" style="color:#D0D0D0">)</span> - <span class="Number" style="color:#EE7F18">15</span><br><span class="indent8">        </span><span class="indent4">    </span>&& Attendance[Dated] <= <span class="Keyword" style="color:#0070FF">EARLIER</span><span class="Parenthesis" style="color:#D0D0D0"> (</span> Attendance[Dated] <span class="Parenthesis" style="color:#D0D0D0">)</span><br><span class="indent4">    </span><span class="Parenthesis" style="color:#D0D0D0">)</span>,<br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">FILTER</span><span class="Parenthesis" style="color:#D0D0D0"> (</span> Attendance, Attendance[Sick Day] = <span class="StringLiteral" style="color:#D93124">"Y"</span> <span class="Parenthesis" style="color:#D0D0D0">)</span>,<br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">FILTER</span><span class="Parenthesis" style="color:#D0D0D0"> (</span><br><span class="indent8">        </span>Attendance,<br><span class="indent8">        </span>Attendance[Clock No] = <span class="Keyword" style="color:#0070FF">EARLIER</span><span class="Parenthesis" style="color:#D0D0D0"> (</span> Attendance[Clock No] <span class="Parenthesis" style="color:#D0D0D0">)</span><br><span class="indent4">    </span><span class="Parenthesis" style="color:#D0D0D0">)</span><br><span class="Parenthesis" style="color:#D0D0D0">)</span><br>

Which is (depending on how well I understood your columns)... for each sick day, add a column which is "from this sick day, and back 15 days... how many other sick days were there for this same person?"

Am I understanding the columns correctly -- and ignoring that we don't want 15 days exactly (its more complicated) -- and ignoring that we don't actually USE this column... uh... are we moving in some direction that is forward'ish?

 

[rileybloke]

Yes your statement is correct. We on course
I have added the DAX you gave me and have uploaded the project again onto my onedrive.
I tested on an employee Carl, filter the year 2014, and sort date newest first, this chap has been off sick since 31st July, and your DAX calculation works until 15th August , the 11th consecutive day, where the count goes a little wrong.
Thanks.

 

[scottsen]

Well, "wrong" is a relative term In the Attendance table, 8/9, 8/10, 8/16, and 8/17 are not there... so we end up with a bunch of "in the last 15 days, you have missed 11"... because we don't have those 4 rows.

I don't think anybody as attendance on those days -- what's up there? Need to mark in your Calendar table as a holiday?

 

[rileybloke]

Its the weekend nobody works the weekend unless they do overtime. I added a column to the calendar table called Workday to identify if the date is a work day.
I extended your filter to identify a workday but it didn't work, probably missed something simple, any ideas? I uploaded revised doc here...
https://onedrive.live.com/redir?resid=BB8EACAB0F04EBE7!1230