Count Number of Instances of Hours Over 40 Per Week

[Anonymous]

In the below table example, I'm trying to count the number of instances where more than 40 hours were recorded by end of the week.  The end result would be 2 instances.  Thank you in advance!

Employee ID	Date	Pay Type	Hours	End of Week	Weekly Total	Count over 40	Hours Moved to Overtime
1	6/1/2021	Regular	8	6/5/2021	40
1	6/2/2021	Regular	8	6/5/2021	40
1	6/3/2021	Regular	8	6/5/2021	40
1	6/4/2021	Regular	8	6/5/2021	40
1	6/5/2021	Regular	8	6/5/2021	40
1	6/6/2021	Regular	8	6/12/2021	48
1	6/7/2021	Regular	8	6/12/2021	48
1	6/8/2021	Regular	8	6/12/2021	48
1	6/9/2021	Regular	8	6/12/2021	48
1	6/10/2021	Regular	8	6/12/2021	48
1	6/11/2021	Regular	8	6/12/2021	48	1	8
1	6/12/2021	Regular		6/12/2021	48
1	6/13/2021	Regular	8	6/19/2021	40
1	6/14/2021	Regular	8	6/19/2021	40
1	6/15/2021	Regular	8	6/19/2021	40
1	6/16/2021	Regular	8	6/19/2021	40
1	6/17/2021	Regular	8	6/19/2021	40
1	6/18/2021	Regular		6/19/2021	40
1	6/19/2021	Regular		6/19/2021	40
1	6/20/2021	Regular	8	6/26/2021	48
1	6/21/2021	Regular	8	6/26/2021	48
1	6/22/2021	Regular	8	6/26/2021	48
1	6/23/2021	Regular	8	6/26/2021	48
1	6/24/2021	Regular	8	6/26/2021	48
1	6/25/2021	Regular	8	6/26/2021	48	1	8
1	6/26/2021	Regular		6/26/2021	48
Total Count						2

[v-jingzhang · Accepted solution]

Hi @Anonymous 

If you already have [End of Week] and [Weekly Total] columns in your table, you can use below measure:

Measure = 
VAR __Table =
    DISTINCT (
        SELECTCOLUMNS (
            'Table',
            "__EmployeeID", [Employee ID],
            "__EndOfWeek", [End of Week],
            "__WeeklyTotal", [Weekly Total]
        )
    )
RETURN
    COUNTROWS ( FILTER ( __Table, [__WeeklyTotal] > 40 ) )

Is this what you want? Let me know if you have any questions.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[Anonymous]

Awesome!  You are the man!

[Greg_Deckler]

@Anonymous Seems like:

Measure = 
  VAR __Table = 
    ADDCOLUMNS(
      SELECTCOLUMNS('Table',"__EmployeeID",[Employee ID],"__Date",[Date],"__Hours",[Hours]),
      "__WeekNum",WEEKNUM([__Date])
    )
  VAR __Table2 = SUMMARIZE(__Table,[__EmployeeID],[__WeekNum],"__WeeklyHours",SUM([__Hours))
RETURN
  COUNTROWS(FILTER(__Table2,[__WeeklyHours]>40))

[Anonymous]

Thanks for the quick turnaround!  Can you share the pbix file?  I'm missing something.