I have a payroll data table of the following form (sample data included at the end of the post).
[ Name, Date, Hours, Job Code ]
Name = Employee Name
Date = Date format, but this is weekly data. So the dates will always be exactly one week apart.
Hours = Number of hours worked for that Job Code for that week ending in the date specified in the [Date] column.
Job Code = Work type. Regular vs OT.
I am tasked with creating a table showing number of employees who've worked over a maximum per-week hours limit (60 hours), but in a monthly format. In other words, it should look like this:
There are a few important conditions:
I have created following measures so far:
// To get the total work hours considering all job codes for an employee for a particular date
HoursPerson = CALCULATE(SUM(WorkHours[Hours]), ALLSELECTED(WorkHours[Job Code]))
Then, I imagined I could create another measure where an employee's name is counted if HoursPerson > 60
, and if I put this in a matrix it would give me the answer I want.
CountOver60 = COUNTAX(VALUES(WorkHours[Name]), [HoursPerson] > 60)
But this doesn't give me the expected answer, instead it counts all names that has at least one value. Below, I have put both measures in a matrix and highlighted HoursPerson > 60, and as you can see, for CountOver60
it counts all names:
What am I doing wrong here? How can I get the desired result?
Sample Data:
Name,Date,Hours,Job Code
Alejandro,2023/08/04,25,REGULAR
Alexander,2023/07/14,20.5,REGULAR
Arceli,2023/09/15,30,REGULAR
Chad,2023/07/14,20,REGULAR
Chad,2023/09/15,21.25,REGULAR
Christopher,2023/08/11,27.5,REGULAR
Christopher,2023/08/18,43,REGULAR
Christopher,2023/08/25,31.75,REGULAR
Daniel,2023/07/21,22,REGULAR
Daniel,2023/08/04,23.5,REGULAR
Daniel,2023/08/11,70,REGULAR
Daniel,2023/08/18,28,REGULAR
Daniel,2023/09/01,21.5,REGULAR
Darrell,2023/09/15,29,REGULAR
David,2023/07/14,21,REGULAR
David,2023/09/01,21,REGULAR
Devin,2023/09/15,65,REGULAR
Everett,2023/07/21,21,REGULAR
Everett,2023/08/25,24.5,REGULAR
Gabriel,2023/08/04,47,REGULAR
Isaias,2023/09/15,20,REGULAR
James,2023/08/11,21.5,REGULAR
Jason,2023/07/21,29.5,REGULAR
Jason,2023/08/25,38.5,REGULAR
Jason,2023/08/25,30,OT
Jeremy,2023/07/14,63,REGULAR
Jeremy,2023/07/21,21,REGULAR
Jeremy,2023/07/28,67,REGULAR
John,2023/07/14,48,REGULAR
John,2023/07/21,28,REGULAR
John,2023/07/28,65,REGULAR
John,2023/08/04,24.5,REGULAR
John,2023/08/11,22,REGULAR
John,2023/08/25,20,REGULAR
John,2023/09/22,31.5,REGULAR
Justin,2023/09/15,31.25,REGULAR
Leonides,2023/08/18,20,REGULAR
Leonides,2023/08/25,22,REGULAR
Leonides,2023/08/25,42,OT
Mario,2023/07/21,22,REGULAR
Mario,2023/07/21,24,OT
Mario,2023/07/28,40,REGULAR
Mario,2023/07/28,24,OT
Matthew,2023/07/28,24.5,REGULAR
Pedro,2023/07/14,20,REGULAR
Robert,2023/08/18,20,REGULAR
Shawn,2023/08/04,24.5,REGULAR
Shawn,2023/09/15,32.75,REGULAR
Steven,2023/07/14,22.5,REGULAR
Thomas,2023/07/21,32,REGULAR
Thomas,2023/07/28,24.5,REGULAR
Thomas,2023/09/15,31.5,REGULAR
Thomas,2023/09/22,26.5,REGULAR
Thomas,2023/07/28,20,OT
Thomas,2023/08/18,45,REGULAR
Thomas,2023/08/18,20.5,OT
Timothy,2023/07/14,20,REGULAR
Timothy,2023/08/11,25,REGULAR
Timothy,2023/08/11,45,OT
Vincent,2023/07/07,20,REGULAR
Vincent,2023/07/28,20,REGULAR
Vincent,2023/08/11,20,REGULAR
Vincent,2023/08/18,20,REGULAR
Vincent,2023/09/01,20,REGULAR
Vincent,2023/09/08,54,REGULAR
Vincent,2023/09/15,65,REGULAR
Vincent,2023/09/22,20,REGULAR
EDIT:
Adding an image to provide an illustration of what needs to be counted.
UPDATE
CountOver60 =
VAR t =
FILTER(
ADDCOLUMNS(
SUMMARIZE(WorkHours, WorkHours[Name], 'Date'[Calendar Year], 'Date'[Month Number], 'Date'[Date]),
"@x", [HoursPerson]
),
[@x] > 60
)
VAR t2 = SUMMARIZE(t, WorkHours[Name], 'Date'[Month Number])
RETURN COUNTROWS(t2)