Count STAFFID who have not submitted timesheets over consecutive weeks

[Deevo_]

Hi All,

I have been searching and finding it hard to adapt similar posts to my situation. I apologise if I am asking the wrong questions here and your help would be great.

Requirements:

1. I would like to create a DAX measure to count the consecutive weeks they have not submitted a timesheet.
2. I would also like to display staff names who have not submitted a timesheet in the past 5 weeks

Dataset:

Week Start	StaffID	StaffName	Recorded Hours	Project Assignment ID	Project Start Date	Project End Date	Active Assignment?
07/05/23	001	Alice	37.5	A1	01/05/23	30/06/23	Yes
14/05/23	001	Alice	37.5	A1	01/05/23	30/06/23	Yes
21/05/23
28/08/23

Dataset Limitations:

• The extracted data I receive only provides details of the actual time that a staff records against their assignments.
• Unfortunately, there is no 'Planned Hours' per week column, if there was, then there would be a row for each week between the project start and end date, which would make this alot easier to do.
• We can only assume based off the project start and end date that they are required to timesheet each week up until the end date of the project.
  • 'Active Assignment' column is derived value. i.e. IF([Project End Date] > [Week Start], 'Yes', 'No')

Expected result:

• Staff Name	Consecutive Weeks with No timesheet
  Alice	2

Many thanks