topic Re: Function to collect leftover hours if exceeding 8 hours of work? in DAX Commands and Tips

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

Tummi — Tue, 21 Jan 2025 02:29:38 GMT

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/2025	Regular	A	5.5
01/20/2025	Overtime	A	3.5
01/20/2025	Regular	B	5
01/20/2025	Regular	B	4

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.

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

bhanu_gautam — Tue, 21 Jan 2025 06:39:31 GMT

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

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

johnt75 — Tue, 21 Jan 2025 11:25:04 GMT

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 )

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

Tummi — Tue, 21 Jan 2025 17:14:09 GMT

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?

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

Tummi — Tue, 21 Jan 2025 17:15:04 GMT

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.

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

johnt75 — Wed, 22 Jan 2025 09:26:57 GMT

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 )