cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

New Member

## Weekly Hours Worked Calculation

I have a data set that brings in each employee's daily hours worked, split into regular and overhead. I have calculations to sum hours worked in a day, week, etc., but am now trying to create a calculation that only sums hours that are regular when exceeding 40 in a given week. I have created a custom column that provides the Start of Week based on the transaction date, which allows for me to build visuals looking at hours on a weekly basis. But in addition to viewing total regular hours, I want to a sum of only those hours over 40 in a given timeframe.

Here is an example of my data set. For the Start of Week 11/5/2023, this employee has 42 RegHrs, in the week of 11/12 has 42 RegHrs again, and in the week of 11/19 has 36. In this example, the calculation should provide 4 hours over 40 regular hours. Is this possible? Thanks in advance for any assistance.

 Employee TransDate RegHrs OHHours StartOfWeek 2610 11/6/2023 8.25 2.5 11/5/2023 2610 11/7/2023 8.5 1.5 11/5/2023 2610 11/8/2023 8.25 1 11/5/2023 2610 11/9/2023 9 0.5 11/5/2023 2610 11/10/2023 8 0.5 11/5/2023 2610 11/13/2023 8.5 1 11/12/2023 2610 11/14/2023 8.5 0 11/12/2023 2610 11/15/2023 7.75 1.5 11/12/2023 2610 11/16/2023 9 0.5 11/12/2023 2610 11/17/2023 8.25 1 11/12/2023 2610 11/20/2023 9 1 11/19/2023 2610 11/21/2023 9.5 0.5 11/19/2023 2610 11/22/2023 8.5 0.5 11/19/2023 2610 11/23/2023 0 8 11/19/2023 2610 11/24/2023 9 1.25 11/19/2023

1 ACCEPTED SOLUTION
Super User

you can try this

``````Measure =
var tbl=SUMMARIZE('Table','Table'[Employee],'Table'[StartOfWeek],"hour",sum('Table'[RegHrs])-40)
return sumx(FILTER(tbl,[hour]>0),[hour])``````

Proud to be a Super User!

3 REPLIES 3
Super User

you can try this

``````Measure =
var tbl=SUMMARIZE('Table','Table'[Employee],'Table'[StartOfWeek],"hour",sum('Table'[RegHrs])-40)
return sumx(FILTER(tbl,[hour]>0),[hour])``````

Proud to be a Super User!

New Member

This is perfect, thank you!

Super User

you are welcome

Proud to be a Super User!

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors