topic Re: Calculate / Flag 12 consecutive days in a 14 day period in DAX Commands and Tips

Calculate / Flag 12 consecutive days in a 14 day period

micpete — Mon, 05 Aug 2024 10:01:54 GMT

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

Re: Calculate / Flag 12 consecutive days in a 14 day period

bhanu_gautam — Mon, 05 Aug 2024 10:21:16 GMT

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")

Re: Calculate / Flag 12 consecutive days in a 14 day period

micpete — Mon, 05 Aug 2024 12:23:35 GMT

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

Re: Calculate / Flag 12 consecutive days in a 14 day period

SachinNandanwar — Mon, 05 Aug 2024 13:11:21 GMT

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

Re: Calculate / Flag 12 consecutive days in a 14 day period

micpete — Mon, 05 Aug 2024 13:18:28 GMT

Hi Sachin,

Yes that is correct.

Re: Calculate / Flag 12 consecutive days in a 14 day period

SachinNandanwar — Mon, 05 Aug 2024 13:30:39 GMT

Hi Pete, Do you have a Date table ?

Re: Calculate / Flag 12 consecutive days in a 14 day period

Anonymous — Tue, 06 Aug 2024 06:45:37 GMT

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

Re: Calculate / Flag 12 consecutive days in a 14 day period

micpete — Tue, 06 Aug 2024 14:21:34 GMT

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.

Re: Calculate / Flag 12 consecutive days in a 14 day period

micpete — Tue, 06 Aug 2024 14:22:33 GMT

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

Re: Calculate / Flag 12 consecutive days in a 14 day period

micpete — Tue, 06 Aug 2024 14:34:56 GMT

This is how I've put it in

Re: Calculate / Flag 12 consecutive days in a 14 day period

micpete — Tue, 06 Aug 2024 15:01:00 GMT

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.

Re: Calculate / Flag 12 consecutive days in a 14 day period

micpete — Tue, 06 Aug 2024 15:01:10 GMT

Re: Calculate / Flag 12 consecutive days in a 14 day period

micpete — Tue, 06 Aug 2024 15:01:20 GMT

Re: Calculate / Flag 12 consecutive days in a 14 day period

micpete — Mon, 12 Aug 2024 14:02:32 GMT

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

Re: Calculate / Flag 12 consecutive days in a 14 day period

micpete — Mon, 19 Aug 2024 11:13:30 GMT

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?

Re: Calculate / Flag 12 consecutive days in a 14 day period

micpete — Tue, 20 Aug 2024 11:52:36 GMT

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