Count active employees with missing timesheets

[metcala]

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!

[metcala]

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 · Accepted solution]

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.

[metcala]

Thank you so much! That is exactly what I was after.

Really appreciate you taking the time to help me out!

[lbendlin]

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-...
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/1447523