Modeling Recoverable Hours and Overtime in dbt/Power BI
20:33 06 Nov 2025

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 and need to fall off the calculation.

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?

powerbi dbt