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

[domdom]

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 ID	Day taken sick
A	17/01/2017
A	29/01/2017
A	17/02/2017
A	09/03/2017
A	18/03/2017
A	22/04/2017
A	23/04/2017
A	06/05/2017
A	16/05/2017
A	22/06/2017
A	28/06/2017
A	03/07/2017
A	20/07/2017
A	15/09/2017
A	18/09/2017
A	16/10/2017
A	25/10/2017
A	18/11/2017
A	30/11/2017
B	17/02/2017
B	16/04/2017
B	18/04/2017
B	24/06/2017
B	11/07/2017
B	28/07/2017
B	24/08/2017
B	23/09/2017
B	06/10/2017
B	17/10/2017
B	16/12/2017
B	18/12/2017
B	28/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:

A	22/04/2017
A	23/04/2017
A	06/05/2017
A	16/05/2017

A	15/09/2017
A	18/09/2017
A	16/10/2017
A	25/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

[Johanno]

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

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

[v-frfei-msft]

Hi @domdom,

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

Regards,
Frank

[Zubair_Muhammad]

@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