Run through records while summing and checking total

[dBrand]

I have timesheet records in a collection (and a table as well) with the following fields:
Employee, Date, Time Worked, Department, PayAmount

I need to create a report (which I display in a Gallery) to show, for each Department, total PayAmount. But, the issue is that the total PayAmount is based upon different rates once a worker goes over 40 hours for the week, which can happen in the middle of a day, and we need to report it by Dept. See example below, to hopefully better lay out the issue and data:

Example (daily time entry):

Employee, Date, Time Worked, Department, RegRate, OTRate
1, 1/1/21, 10, DeptA, $10, $25
1, 1/2/21, 10, DeptA, $10, $25
1, 1/3/21, 10, DeptB, $15, 30
1, 1/4/21, 5, DeptB, $15, $30
1, 1/5/21, 10, DeptA, $10, $25

It has to sum up the PayAmounts (Time X RegRate) up to 40 hours and starting at 41 hours and up use OT Rate.
It needs to go in Date order, since the rates could change at any point during the week they are working, once they pass the 40 hour mark. But this isn't known until the end of the week when they run the report, as it then totals the hours worked and figures out the pay for each worker, and at the same time, breaks out the PayAmount by location for accounting.

So, for the above, you would get:

On Payroll Report:
Employee 1 would have:
20 hrs x $10=$200 (lines 1 & 2) towards DeptA, plus
15 hrs x $15=$225 (lines 3 & 4) towards DeptB, plus
5 hrs x $10=50 (part of line 5 to come up to 40 hours) towards towards DeptB, plus
5 hrs x $25=$125 (part of line 5 once we past 40 hours for the week) towards DeptA,

Therefore, the report would show:
DeptA: $325
DeptB: $275

I can't seem to find a way to come up with a way to collect this information from the daily time entries to tally the hrs worked day by day (in date order) to properly do the above calculations. Date order is important, or the PayAmount would be different. I saw from the ForAll function that it cannot be assumed it will run through records in any specific order, so that method (although it would be really slow) is not an option.

Any help would be greatly appreciated! Thanks.

[v-qiaqi-msft · Accepted solution]

Hi @dBrand,

According to your example, I think there is a small problem with your calculation result.

I assume that January 1st to January 5th is a week.

I have a list . The data in the list comes from your example. Please see it in the image. 

Firstly, I want to find the record with the 41st hour. 

Add a button control and set its onselect property to:

ClearCollect(Col,0);ClearCollect(Col2,0);ClearCollect(TheRecord,Filter(ForAll(Sort(LIST144,Date,Ascending),Patch(Col2,First(Col2),{Value:First(Col).Value});Patch(Col,First(Col),{Value:First(Col).Value+ThisRecord.'Time Worked'});If(41>First(Col2).Value&&41<=First(Col).Value,ThisRecord,Blank())),!IsBlank(Employee)))

// Col2: Save the total working time of (N-1)th  

// Col: Save the total working time of Nth

// TheRecord: find the record with the 41st hour

For example:

If I am calculating the third record, the value of Col2 is 10+10=20 and the value of Col is 10+10+10=30.

After pressing the button, I found the record.

Secondly ,calculate payments by different departments in a week.

Add a label control and set its Text property to:

Sum(Filter(LIST144,Date<First(TheRecord).Date,Department="DeptA"),'Time Worked')*10+Sum(Filter(LIST144,Date>First(TheRecord).Date,Department="DeptA"),'Time Worked')*25+If(First(TheRecord).Department="DeptA",(40-Sum(Filter(LIST144,Date<First(TheRecord).Date),'Time Worked'))*10+(First(TheRecord).'Time Worked'-(40-Sum(Filter(LIST144,Date<First(TheRecord).Date),'Time Worked')))*25,0)

Add another label control and set its Text property to:

Sum(Filter(LIST144,Date<First(TheRecord).Date,Department="DeptB"),'Time Worked')*15+Sum(Filter(LIST144,Date>First(TheRecord).Date,Department="DeptB"),'Time Worked')*30+If(First(TheRecord).Department="DeptB",(40-Sum(Filter(LIST144,Date<First(TheRecord).Date),'Time Worked'))*15+(First(TheRecord).'Time Worked'-(40-Sum(Filter(LIST144,Date<First(TheRecord).Date),'Time Worked')))*30,0)

The result is as follows:

[dBrand]

Thanks for the work to figure this out. One concern I have is supposedly the ForAll function does not necessarily go in any particular order, and you cannot assume it will go sequentially (according to the documentation), unless I am reading this wrong. But if that really isn't a concern, then I just need to update your code to do this for all Workers and Depts (without hardcoding the rate or Depts, as those are updated in separate user-defined tables. Thanks again for this.