Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Deevo_
Resolver I
Resolver I

Count STAFFID who have not submitted timesheets over consecutive weeks

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 StartStaffIDStaffNameRecorded HoursProject Assignment IDProject Start DateProject End DateActive Assignment?
07/05/23001Alice37.5A101/05/2330/06/23Yes
14/05/23001Alice37.5A101/05/2330/06/23Yes
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 NameConsecutive Weeks with No timesheet
    Alice

    2

     

Many thanks

0 REPLIES 0

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors