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

Reply
vandelD
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.

 

EmployeeTransDateRegHrsOHHoursStartOfWeek
261011/6/20238.252.511/5/2023
261011/7/20238.51.511/5/2023
261011/8/20238.25111/5/2023
261011/9/202390.511/5/2023
261011/10/202380.511/5/2023
261011/13/20238.5111/12/2023
261011/14/20238.5011/12/2023
261011/15/20237.751.511/12/2023
261011/16/202390.511/12/2023
261011/17/20238.25111/12/2023
261011/20/20239111/19/2023
261011/21/20239.50.511/19/2023
261011/22/20238.50.511/19/2023
261011/23/20230811/19/2023
261011/24/202391.2511/19/2023

 

 

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@vandelD 

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

11.PNG





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

Proud to be a Super User!




View solution in original post

3 REPLIES 3
ryan_mayu
Super User
Super User

@vandelD 

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

11.PNG





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

Proud to be a Super User!




This is perfect, thank you!

you are welcome





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

Proud to be a Super User!




Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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