Calculate / Flag 12 consecutive days in a 14 day period

[micpete]

Hi,

I have a database of operatives that work at our site, they come onto site and we can see what days they have come onto site - I need to work out if they have come onto site 12 days in a row within a 14 day period then mark that as a red flag / indicator, how can I do this - this is the information I have below: operative ID is the ID of the individual, the date is when they came on site (this is just dummy data, the table is far more extensive with site name etc):

Operative ID	Date
1	02-Sep-22
2	03-Aug-24
3	17-Jul-24
4	02-Feb-23
5	22-Oct-23
6	02-Dec-23
7	17-Jun-21

[micpete]

Is anyone able to help me? I've been stuck on this for too long please?

[micpete]

Hi Banu,

Just to come back here, in the image I have attached to this, you can see that this individual worked from Jan 1st to 4th, then 9th to 12th - however he was awat on the 5th, 6th 7th , 8th. Then away again 13th, 14th - which means he did not work more than 12 days within those 14 days and so he shouldn't flag as red

[Anonymous]

Hi @micpete ,

How do you define these 14 days, for example, July 1 and July 13 as a period or can July 2 also be a period with July 14. Please provide sample data as well as expected results.

Best regards,
Community Support Team_ Scott Chang

[micpete]

Hello,

So if an individual comes onto site on from July 1 to July 14 with no day off so comes everyday, then that person needs to be marked as red because they've been to site more than 12 days with no days off.

If an individual comes to site for only 12 days from July 1st to July 14 that person has done 12 or less days between that period and have no flag.

The 14 day period can start from any day of the month or even roll onto the next month - it doesn't matter when the 14 day period starts, what matters is that they are only doing no more than 12 days within that 14 day period.

[micpete]

I think if the count of the day reaches 13 / 14 then I want to flag it as red basically, the count goes back a day when there is a day missing and if the count goes over 14 then it resets to 1 again and the period starts over.

[SachinNandanwar]

So would there be a multiple date entries for every individual operative ID that comes to site ?

For example : OperativeID >>1 
would have entries like 
02-Sep-22
03-Sep-22
04-Sep-22
05-Sep-22

[micpete]

Hi Sachin,

Yes that is correct.

[SachinNandanwar]

Hi Pete, Do you have a Date table ?

[micpete]

I do yes, I have a date table that has a many to one relationship with the operative ID table

[micpete]

I think if the count of the day reaches 13 / 14 then I want to flag it as red basically, the count goes back a day when there is a day missing and if the count goes over 14 then it resets to 1 again and the period starts over.

[micpete]

Hi Banu,

The formula works but the output seems wrong - is it because my data is missing dates to show a full date range?

For example the individual below shows red flag present on 31, 1, 2, 3 - but the individual didn't work more than 12 days in a 14 day period

[micpete]

This is how I've put it in

[micpete]

I think if the count of the day reaches 13 / 14 then I want to flag it as red basically, the count goes back a day when there is a day missing and if the count goes over 14 then it resets to 1 again and the period starts over.

[bhanu_gautam]

@micpete , You can create a calulated column using

ConsecutiveDaysFlag =
VAR CurrentOperative = 'Table'[Operative ID]
VAR CurrentDate = 'Table'[Date]
VAR DateRange =
FILTER(
'Table',
'Table'[Operative ID] = CurrentOperative &&
'Table'[Date] >= CurrentDate - 13 &&
'Table'[Date] <= CurrentDate
)
VAR ConsecutiveDays =
COUNTROWS(
FILTER(
DateRange,
DATEDIFF(
MINX(DateRange, [Date]),
[Date],
DAY
) <= 13
)
)
RETURN
IF(ConsecutiveDays >= 12, "Red Flag", "No Flag")

[micpete]

Hi Banu,

I'd thought I'd try again, I can see that in my data because an individual might have come to site more than once on a given day, it counts each visit in the final result, so for example below I can see that an individual has entered site 3 times on the 17th June, so it has counted the days 3 times plus the 14th and 15th, I only want it to count it once, so if they've been on site on the 17th June at least once, then that is only one day, not three. Is this possible to do?