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
