cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
domdom Regular Visitor
Regular Visitor

Identifying 4 instances of sickdays that fall in a 6 week period - Expert DAX help required!!

Hi

 

I'm having a lot of issue working out the Dax formula for the following.  I have a table and shows employees and any days that they have taken sick from work. e.g.

 

Employee IDDay taken sick
A17/01/2017
A29/01/2017
A17/02/2017
A09/03/2017
A18/03/2017
A22/04/2017
A23/04/2017
A06/05/2017
A16/05/2017
A22/06/2017
A28/06/2017
A03/07/2017
A20/07/2017
A15/09/2017
A18/09/2017
A16/10/2017
A25/10/2017
A18/11/2017
A30/11/2017
B17/02/2017
B16/04/2017
B18/04/2017
B24/06/2017
B11/07/2017
B28/07/2017
B24/08/2017
B23/09/2017
B06/10/2017
B17/10/2017
B16/12/2017
B18/12/2017
B28/12/2017

 

 

For each employee - i need to flag (some how - i dont care how) - where they have had a period of 4 sickdays that fall within a 6 week period.  For example - from my table above - the following all fall within a 6 week period for employee A:

 

A22/04/2017
A23/04/2017
A06/05/2017
A16/05/2017

 

 

A15/09/2017
A18/09/2017
A16/10/2017
A25/10/2017

 

 

and so I need to flag these somehow and pull out the dates.  I don't mind how.

 

No date should be used twice though - so once a date has been flagged as part of a 4 instance within 6 week series - then they should not be used again in another 4 instance within 6 week series....as such the dates should be used in date order (earliest to latest)

 

Please let me know if you can help

 

thanks

3 REPLIES 3
Super User
Super User

Re: Identifying 4 instances of sickdays that fall in a 6 week period - Expert DAX help required!!

@domdom

 

Why in above example these set of dates are excluded?

 

 

A 22/06/2017
A 28/06/2017
A 03/07/2017
A 20/07/2017
Highlighted
Johanno Member
Member

Re: Identifying 4 instances of sickdays that fall in a 6 week period - Expert DAX help required!!

Hi, try this as a calculated column:

Count sick days within 6 weeks = 
VAR CurrentDateToCheckMin = SickDates[Day taken sick]
VAR CurrentDateToCheckMax = SickDates[Day taken sick]+6*7
VAR EmployeeID = SickDates[Employee ID]
RETURN CALCULATE(COUNTROWS(SickDates);FILTER(SickDates;SickDates[Day taken sick]<CurrentDateToCheckMax && SickDates[Day taken sick]>CurrentDateToCheckMin);SickDates[Employee ID]=EmployeeID)+1

Capture.JPG

I don't have the same date formats as you though.

 

Then you can highlight one of the dates in the intervals by filtering with value 4 or more.

 

/Johan

 

 

Community Support Team
Community Support Team

Re: Identifying 4 instances of sickdays that fall in a 6 week period - Expert DAX help required!!

Hi @domdom,

 

Does that make sense? If so, kindly mark the answer as solution to close the case.


Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.