Search code examples
powerbidaxpowerbi-desktopmeasure

Measure to get a monthly count using weekly data


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: enter image description here

There are a few important conditions:

  • For the hours worked, must consider all pay codes. In other words, total hours per employee considering both regular and OT work hours.
  • In the case of a certain employee working more than 60 hours/week two or more weeks in the same month (such as in the case of Jeremy in July in the data set below), still count that as only one entry per that month.
  • The maximum calculation is strictly geater than (so exactly 60 hours a week doesn't count for above calculation).

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: enter image description here

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. enter image description here


Solution

  • UPDATE

    enter image description here

    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)