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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Deevo_
Helper II
Helper II

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors