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 employee during that week and do not have an approved timesheet for that week:
| Employee Name | Week ending - 2 April 2023 | Week ending - 9 April 2023 | ... |
| Abc Def | 0 | 1 | ... |
| Ghi Jkl | 1 | 1 | ... |
In terms of the data structure I have the following:
Employee table
Employee ID
Employee Name
Role ID
Start Date
End Date
Timesheet table
Timesheet ID
Employee ID
Period End
Total Hours
Status
Date table
Date linked to timesheet table (period end) and employee table (start date and end date)
I have tried creating a measure but am struggling to get it working.
Here is my current attempt which should just show the total approved hours.
Total Approved hours =
VAR _date = MAX('Timesheets'[Period End])
RETURN
CALCULATE(
SUM('Timesheets'[Total Hours],
FILTER(
'Employees',
'Employees'[Start Date] <= _date && 'Employees'[End Date] > _date
),
FILTER(
'Timesheets',
'Timesheets'[Status] = "Approved"
)
)
Any help would be greatly appreciated!
Thanks!
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.
4 Replies
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Thanks 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!
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!
