A third of the team had logged no time two days before month end
The business runs on a bespoke time and billing system built over several years by a single in-house developer and shaped by many stakeholders. It held all the data but no reporting layer: staff couldn't find their numbers, queries were slow, and the control report used to chase timesheets no longer matched the 50-hour-week policy. On 29 September, 14 of a 42-person project team had entered no time at all for the month.
Report on the data, leave the application alone
Rather than add more complexity to the in-house system, we built the reporting in Power BI on a read-only connection through an on-premises gateway, refreshed outside business hours so reporting never slows the live system. We mapped the schema with the client's developer, agreed a mock-up before building, and modelled the data as a star schema that the forecast report shares.
Billable vs internal time
Any project that belongs to the firm's own internal client counts as non-chargeable time; every other project is client work. We confirmed the rule with the client's developer before building on it.
Targets that follow leave
The in-house system already works out each person's required client hours for the month. Using that figure means booking a holiday lowers the target instead of making someone look behind.
Current month first
Staff open the report on this month and see which days are complete, which are short, and the entries and notes behind each day. Earlier months are one click away.
Project before client
The firm looks at work by project, so the report lands there and drills up to client. Non-chargeable time is split into its internal codes, so juniors can see their training time and seniors their business development time.
Filters for managers
Managers narrow the view to a client, project, team leader or grade group, select several months to spot a trend, and export any table to Excel.
Row-level security
One report serves everyone. Staff sign in with their work account and see their own time, team leaders see their team, and management sees the whole business.
Where the data lives and how it flows
The client's in-house time and billing database, read through a dedicated read-only account
What changed
Two weeks after kick-off, the firm had a working report that every member of staff can open to see where they stand this month, and that managers can use to see who needs a reminder before the month closes, not after.
The figures come straight from the in-house system, so the project team and finance work from the same numbers. Next, the firm checks the report against the system's own utilisation figures before it goes out to the whole business through the Power BI app, and the same data model now carries the forecast report.