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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Tummi
Frequent Visitor

Function to collect leftover hours if exceeding 8 hours of work?

I'm trying to configure a formula to calculate the 'Overage' Hours of a days work. From my data I did a 'GroupBy' within PowerQuery to condense all my columns to only the necessary fields to narrow down my calculation for easier functionality.

However, I'm stuck on this final piece on this calculation.

 

The goal is to be able to track how much time is worked past 8 hours (if you work 9 hours in 1 day, your overage hours would be 1 hour). In addition, a regular work week being 40 hours, I can exceed 40 hours and go into overtime within the same work day. I don't know how to manage this formula to sum 'Regular' hours and 'Overtime' hours together and subtract it from 8 to get the leftover hours worked.

 

My current formula is super simple as I keep trying to build on it, but don't know how to go about it.

 

=IF(SUM[Hours]-8<=0,"",SUM[Hours]-8)

 

Date Category Person Hours

01/20/2025RegularA5.5
01/20/2025OvertimeA3.5
01/20/2025RegularB5
01/20/2025RegularB4

Person A 1/20/2025 = 1 Overage hour (but 3.5 hours of Overtime)

 

Person B 1/20/2025 = 1 Overage hour (No overtime accumulated yet)

 

This is over thousands of rows of variances of hours worked.

 

Apologies if it doesn't make much sense I can try to clarify more if asked. But I'm still fairly a novice at DAX and Power Query but would love some insight and advice on how to get this solved.

5 REPLIES 5
johnt75
Super User
Super User

You could create a measure like

Overage hours =
SUMX (
    VALUES ( 'Date'[Date] ),
    VAR TotalHours =
        CALCULATE ( SUM ( 'Table'[Hours] ), REMOVEFILTERS ( 'Table'[Category] ) )
    VAR Result =
        IF ( TotalHours > 8, TotalHours - 8 )
    RETURN
        Result
)
Tummi
Frequent Visitor

I should have also mentioned that within the category there are other categories aside from 'Regular' and 'Overtime'. I looked into KEEPFILTERS and FILTER but haven't been able to mimic your code to output data towards it. I do think this is a step in the right direction. Any suggestions?

If you only want to count the regular and overtime categories you could use

Overage hours =
SUMX (
    VALUES ( 'Date'[Date] ),
    VAR TotalHours =
        CALCULATE (
            SUM ( 'Table'[Hours] ),
            'Table'[Category] IN { "Regular", "Overtime" }
        )
    VAR Result =
        IF ( TotalHours > 8, TotalHours - 8 )
    RETURN
        Result
)
bhanu_gautam
Super User
Super User

@Tummi To calculate the 'Overage' hours for each person on each day, you can use Power Query to transform your data and then apply a custom formula. Here's a step-by-step approach to achieve this:

Group By Date and Person:

Go to the Home tab.
Click on Group By.
Group by Date and Person.
Add a new column for the sum of Hours.

 

Go to the Add Column tab.
Click on Custom Column.
Use the following formula to calculate the overage hours:

if [Sum of Hours] > 8 then [Sum of Hours] - 8 else 0

 

Here is a more detailed example of the steps in Power Query:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
GroupedRows = Table.Group(Source, {"Date", "Person"}, {{"Total Hours", each List.Sum([Hours]), type number}}),
AddedCustom = Table.AddColumn(GroupedRows, "Overage Hours", each if [Total Hours] > 8 then [Total Hours] - 8 else 0),
RenamedColumns = Table.RenameColumns(AddedCustom,{{"Total Hours", "Sum of Hours"}})
in
RenamedColumns

 

 




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Thanks for the help! The table I provided the example was already grouped to specific columns I needed. So I'm glad I'm getting closer to the results I'm needing.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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