Forum Discussion
Count active employees with missing timesheets
Hi I have had a look around the forum for help but can't find any solutions that quite match my scenario. I am looking to create a PowerBI matrix as below with 1 to show they were an active e...
feel free to modify as needed. And please don't use "moon dates". Leave the end date blank if it is not known. Otherwise you create a lot of unnecessary entries in the calendar table.
Thanks for the response.
For simplicity of the example data I will use months rather than weeks
Employee table
| Employee ID | Employee Name | Role ID | Start Date | End Date |
| 123 | John Smith | A001 | 1/1/2020 | 1/1/2099 |
| 124 | Sally Jones | A002 | 1/1/2020 | 29/2/2020 |
| 124 | Sally Jones | A003 | 1/3/2020 | 1/1/2099 |
| 125 | Paul Howard | A004 | 1/4/2020 | 31/5/20 |
Timesheet table
| Timesheet ID | Employee ID | Period | Total Hours | Status |
| 1001 | 123 | 31/1/20 | 165 | Approved |
| 1002 | 123 | 29/2/20 | 160 | Approved |
| 1003 | 123 | 31/3/20 | 150 | Approved |
| 1004 | 123 | 30/4/20 | 170 | Approved |
| 1005 | 123 | 31/5/20 | 150 | Approved |
| 1006 | 123 | 30/6/20 | 160 | Pending |
| 1007 | 124 | 31/1/20 | 170 | Approved |
| 1008 | 124 | 29/2/20 | 165 | Approved |
| 1009 | 124 | 31/3/20 | 160 | Pending |
| 1010 | 124 | 31/5/20 | 150 | Approved |
| 1011 | 124 | 30/6/20 | 170 | Approved |
| 1012 | 125 | 30/4/20 | 150 | Approved |
| 1013 | 125 | 31/5/20 | 160 | Approved |
Expected outcome
To briefly explain the expected outcome:
- If the employee is active and there is not an approved timesheet (i.e. no timesheet or a pending timesheet) there should be a 1
- If the employee is active and there is an approved timesheet there should be a 0
- If the employee is not active during that period it should remain blank
| Employee Name | 31/1/20 | 29/2/20 | 31/3/20 | 30/4/20 | 31/5/20 | 30/6/20 | ... |
| John Smith | 0 | 0 | 0 | 0 | 0 | 1 | |
| Sally Jones | 0 | 0 | 1 | 1 | 0 | 0 | |
| Paul Howard | 0 | 0 |
Thanks!
- lbendlin
Super User
feel free to modify as needed. And please don't use "moon dates". Leave the end date blank if it is not known. Otherwise you create a lot of unnecessary entries in the calendar table.
Thank you so much! That is exactly what I was after.
Really appreciate you taking the time to help me out!