Hello Rodrigo Reis,

Thanks for sharing your suggested model and the work you put into this!

I've tested the logic with our data, and unfortunately, it's not currently producing the correct results because it appears to be treating each week in isolation.

The calculation must be cumulative, meaning it needs to take into account the settled OT hours and the final owed/reserved hours balance carried over from all previous weeks.

Core Issue: Missing Week-Over-Week Carryover

The model is failing to correctly utilize the historical owed_hours_remaining to offset current-week surplus hours before determining the OT_payable amount.

Here are two clear counter-examples showing the discrepancy:

1. Employee 1 (Week 5)

The expected result reflects prior weeks' transactions that should have reduced the owed balance, but the model output is too high.

2. Employee 2

The model incorrectly identifies 12 hours as payable OT when the surplus hours should first go toward clearing the outstanding owed balance.

Expected (With Cumulative Carryover):

Owed remaining: 4
OT_possible/OT payable: 0

Output of your model:

Owed remaining: 16
OT_possible/OT payable: 12

The key adjustment needed is to ensure the previous week's owed_hours_remaining is the baseline for the current week's calculation. Any new surplus OT should reduce that owed balance first, before being classified as payable OT.

Please let me know if you can integrate that historical carryover step into your logic!