# Find consecutive dates in table



## rileybloke (Aug 21, 2014)

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 (Aug 21, 2014)

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 (Aug 22, 2014)

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 (Aug 27, 2014)

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 (Aug 27, 2014)

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 (Aug 27, 2014)

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 (Aug 28, 2014)

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 (Aug 29, 2014)

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 (Aug 29, 2014)

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 (Sep 1, 2014)

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


----------



## rileybloke (Aug 21, 2014)

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 (Sep 1, 2014)

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 (Sep 2, 2014)

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 (Sep 2, 2014)

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


----------



## rileybloke (Sep 4, 2014)

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 (Sep 4, 2014)

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 (Sep 4, 2014)

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 (Sep 5, 2014)

oh yes! simples! thank you very much


----------



## Abhay Gadiya (Mar 13, 2015)

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 (Mar 17, 2015)

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.


----------

