Modeling Recoverable Hours and Overtime in dbt

Help
1

Hi everyone,

I’m working on a reporting model that tracks recoverable hours and overtime for staff under a specific contractual agreement. I’d appreciate any advice on how you would approach modeling and visualizing this in dbt and Power BI.

The business logic:

  • Staff are paid a minimum of 40 hours per week.
  • Any hours above 40 in a given week are paid as Overtime (OT) — but only if the employee has no owed hours carried forward from previous weeks.
  • If an employee works fewer than 40 hours, the shortfall becomes owed hours.
  • A maximum of 12 hours can be recovered in any week.
  • Owed hours expire after 4 weeks if not recovered.

The goal:

I want to report on recoverable hours for the current week — showing how much can be recovered, how much expires, and what remains owed.

I plan to model this in dbt and visualize it in Power BI, but I’m unsure of the best approach to:

  • Track owed hours and their expiry across multiple weeks
  • Calculate recoverable vs. expired hours dynamically
  • Ensure correct overtime calculation based on owed-hour status

Example data

employee_id week_id hours_worked_this_week
1 1 42
2 1 52
3 1 0
1 2 22
2 2 52
3 2 40
1 3 57
2 3 12
3 3 40
1 4 32
2 4 52
3 4 40
1 5 52
2 5 52
3 5 52

Example outputs:

Employee 1
week_id hours_worked_this_week OT paid owed_hours
1 42 2 0
2 22 0 18
3 57 5 6
4 32 0 14
5 52 0 2
Employee 2
week_id hours_worked_this_week OT paid owed_hours
1 52 12 0
2 52 12 0
3 12 0 28
4 52 0 16
5 52 0 4
Employee 3
week_id hours_worked_this_week OT paid owed_hours
1 0 0 40
2 40 0 40
3 40 0 40
4 40 0 40
5 52 12 0

Question:
How would you model and calculate this logic in dbt to make it scalable for Power BI reporting — especially for tracking recoverable and expiring hours across multiple weeks?