Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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])``````

Did I answer your question? Mark my post as a solution!

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])``````

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

New Member

This is perfect, thank you!

Super User

you are welcome

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Helpful resources

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - June 2024

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

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors