Count STAFFID who have not submitted timesheets over consecutive weeks

Deevo_ — Mon, 29 May 2023 02:09:04 GMT

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:

I would like to create a DAX measure to count the consecutive weeks they have not submitted a timesheet.
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

Staff Name	Consecutive Weeks with No timesheet
Alice	2

topic Count STAFFID who have not submitted timesheets over consecutive weeks in DAX Commands and Tips

Count STAFFID who have not submitted timesheets over consecutive weeks