Construction WIP Reporting in Power BI: A Controller's Guide

Short answer: A construction WIP report in Power BI calculates, for every open job, percent complete (cost to date ÷ estimated cost at completion), earned revenue (contract × percent complete), and the gap between earned and billed: over billing or under billing. Contract, cost and forecast data usually comes from your project management system, such as Procore. Billing and job cost come from accounting. To automate it reliably, join the two systems on a project crosswalk, validate the data before every refresh, and snapshot each month-end so reported figures never move.

This guide is for controllers, CFOs and the people who build their reports. It covers the math, the data you need, the mistakes that make a WIP schedule look right while being wrong, and how we automate it in Microsoft Fabric and Power BI.

What a WIP report is, and why it matters

A work-in-progress (WIP) report, or WIP schedule, is a job-by-job view of every contract that is not finished. For each job it shows what you expect to earn, what you have earned so far based on progress, and what you have billed. The difference between earned and billed is where most of the value lives.

Three groups depend on it:

• Controllers and CFOs use it to recognize revenue under percentage-of-completion accounting and to close the month.
• Owners and operations leaders use it to see which jobs are losing margin before the job is over.
• Sureties and lenders read it to judge whether the company is billing ahead of its work, behind it, or hiding a fade.

For most contractors the WIP schedule is still an Excel workbook. Someone exports from the project system, exports from accounting, pastes both into tabs and reconciles by hand every month. That is the process we are usually asked to replace.

The core WIP formulas

Most contractors use the cost-to-cost input method: progress is measured by how much of the expected cost has been spent. These are the definitions we use in our Power BI builds, and they tie to what a controller already prepares.

A worked example

Take one hypothetical job, numbers chosen to keep the math easy:

1. Revised contract: $1,000,000. EAC: $850,000.
2. Cost to date: $425,000, so percent complete is 425,000 ÷ 850,000 = 50%.
3. Earned revenue: $1,000,000 × 50% = $500,000.
4. Billed to date: $560,000, so the job is over billed by $60,000.
5. Gross profit at completion: $150,000, or 15%.

Next month the project manager raises EAC to $900,000. GP % at completion drops from 15% to 10%: that 5-point change is the fade. With cost to date unchanged, percent complete drops to about 47.2% (425,000 ÷ 900,000), earned revenue falls to about $472,000, and over billing grows to about $88,000, even though nobody sent a new invoice. This is why a WIP report is only as good as the cost-to-complete forecast behind it.

Over billing vs under billing in plain terms

Over/under billing is one of the lines sureties and lenders read most closely. Modest over billing on a healthy job is normal and helps cash flow. Persistent under billing is often a warning sign: costs are running ahead of what you can bill, change orders are not being processed, or billing is simply late. Either way, the WIP report should show the net figure and the gross over and under amounts separately, because a large over billing on one job can hide a large under billing on another.

Where the WIP data comes from

A WIP schedule mixes two kinds of data: forecast data that lives with the project team, and financial data that lives in accounting. In a Procore shop the split usually looks like this:

Accounting can be QuickBooks Online, Sage 100 Contractor or another ERP. In our Procore, QuickBooks and HubSpot build, Procore drives contract, EAC, cost to date and billing, and QuickBooks job cost is compared against Procore cost to date as a separate cost variance measure by project. A difference is a finding to review, not automatically an error. In our monthly report rebuild on Procore, Sage and Outbuild, Sage 100 Contractor provides AR and AP through read-only SQL access.

The hard part is not pulling the data. It is joining it. Procore, accounting and a CRM rarely share a project ID, so you need a crosswalk that says which Procore project is which accounting job.

How to build an automated WIP report in Power BI

This is the sequence we follow. It works whether you start with two systems or five.

1. Agree the definitions first. Write down, with the controller, how percent complete, earned revenue, over/under billing and fade are calculated, including edge cases: what happens with no EAC, a negative margin, or cost above EAC. The table above is a good starting point.
2. Choose a system of record for each input. Decide where contract, EAC, cost to date and billed to date come from, and which system is only a cross-check.
3. Extract through the APIs, read-only. In our builds, Procore uses a custom app with its own client ID and secret, and QuickBooks Online uses an Intuit app with the OAuth authorization code flow. We plan for that QuickBooks authorization to need a fresh sign-in roughly every 100 days, so give it an owner and a reminder.
4. Land the raw data first. Store API payloads as received (bronze), then type and validate them (silver), then model them as a star schema (gold). This is the medallion architecture. When a transformation is wrong, you re-run it from bronze instead of re-pulling from the API.
5. Build the project crosswalk. Apply match rules in a strict order: the controller's manual mapping wins, then an exact project number match, then a name match only when it is unambiguous. Anything unmatched stays in the totals and is flagged.
6. Write the measures once, in the semantic model. Every page should use the same governed measures in the semantic model. If two pages calculate earned revenue differently, someone will notice in a meeting.
7. Gate the refresh on data-quality checks. Run checks before anything publishes. A failure that would make a number wrong should stop the refresh, and the report should keep showing the last good version with a visible note that the latest run failed.
8. Snapshot month-end. Store one WIP row per project per month. Month-end figures must stay as reported, even after the forecast changes. Fade and gain are calculated from these snapshots.
9. Build the pages people actually use. At minimum: a portfolio view, the WIP schedule itself, a per-project drill-down by cost code, backlog, and an exceptions page.
10. Reconcile against the old workbook before switching. Run the new report and the Excel WIP side by side for at least one close. Every difference is either a bug in the new build or a bug in the old one, and both are worth finding.

Common WIP reporting mistakes

These are the failure modes we design against. Several came straight out of our own builds.

A WIP schedule that balances and is still wrong

When we pointed one build at live data, a budget column whose name contained spaces silently returned zero. The resulting WIP schedule satisfied every accounting identity, because over billing minus under billing still equaled billed minus earned. It was also completely wrong. Internal consistency is not correctness. Add checks that compare totals to the source, and check that key columns are not unexpectedly all zero.

Missing projects look like quiet projects

A project missing from a financial system often does not throw an error. It shows up as $0 revenue and $0 billing, which looks exactly like a job with no activity. We now treat source coverage as a first-class report page: which projects are present, missing or unmatched in each system. Unmatched projects stay in totals and appear on an exceptions list. In our design principles this is "flag, never drop."

Blank and zero treated the same

A job with no EAC entered is not a job with zero cost to complete. If the report fills blanks with zero, percent complete and margin come out absurd. We label these jobs "No forecast" rather than calculating a number we know is wrong: "blank, never zero."

Percent complete above 100%

When cost to date passes EAC, cost-to-cost percent complete goes above 100% and earned revenue goes above contract. In our builds percent complete is capped at 100%, and those jobs are surfaced as over their estimate at completion so someone updates the forecast. A job with a projected loss also needs accounting attention: under percentage-of-completion accounting, an anticipated loss on a contract is generally recognized in full when it becomes known. Confirm the treatment with your CPA.

Recalculating history

If the report only holds the current forecast, every past month changes when an EAC moves. Your board pack from March no longer matches March. Monthly snapshots fix this, and they are the only honest way to show fade and gain.

Counting the pipeline as backlog

Signed work belongs in backlog. A CRM deal at 60% probability does not. In our Procore, QuickBooks and HubSpot report, won work enters only through Procore, and "total forward work" shows backlog and weighted pipeline side by side, never added together.

Hidden scoring and formula errors in the old workbook

When we rebuilt one contractor's monthly report, we found two scoring formulas in the original Excel workbook that were wrong in ways that happened to offset each other on the sample project, so the total looked right. Spreadsheet logic is rarely tested. Moving it into a governed model with explicit, testable rules is often where the first real findings come from.

What a good Power BI WIP report includes

From our builds, these pages earn their place:

• Portfolio: every open job with revised contract, EAC, percent complete, GP % at completion and a simple risk label (for example: no forecast, at risk below 0%, watch below 10%, otherwise on track).
• WIP schedule: the controller's deliverable, with revised contract, original budget, EAC, cost to date, percent complete, earned revenue, billed to date, over billing, under billing and gross profit.
• Project drill-down: budget vs committed vs cost to date vs EAC by cost code, projected over/under by cost code, fade/gain by month, and the change-order log.
• Backlog and burn: backlog value and backlog months by project and month.
• Exceptions: projects at risk, projects over EAC, cost codes projected over, unmapped projects, and the cost variance between project and accounting systems.
• Billing and retainage, AR and collections, and a cash forecast built from issued invoices and bills, so the WIP is read alongside cash.

The WIP schedule above is from our demo build, loaded with sample data for a fictional contractor of about 20 projects and $232M of contract value. On that data it shows $130M cost to date, $137M billed, $1.6M over billed and $3.7M under billed, with the Procore-vs-QuickBooks cost variance alongside.

How we approach WIP reporting

We build WIP reporting as code, not as a one-off workbook or a single PBIX file. The pattern is the same across our builds, and is laid out on our approach page:

• Microsoft Fabric lakehouse with notebooks for extraction, a registry of API endpoints so new Procore endpoints are a configuration change, and rate limiting built into the extract.
• Bronze, silver and gold layers so raw payloads are kept and every transformation can be re-run.
• A quality gate before publish. In our Procore, QuickBooks and HubSpot build, data-quality checks run before every refresh, backed by 53 tests and 203 offline assertions; a blocking failure stops the refresh and the report keeps the last good version. Our monthly report rebuild on Procore, Sage and Outbuild uses 63 data-quality expectations to stop bad data before it reaches the report.
• A governed semantic model (68 measures in the QuickBooks build, 99 in the monthly report rebuild) that every page reads from.
• Everything in GitHub, including the Power BI report definition, so changes are reviewed as diffs.

The Procore, QuickBooks and HubSpot walkthrough shows the full WIP build end to end, including the crosswalk. The monthly report rebuild shows the same approach replacing an 11-tab Excel workbook with around 700 manually maintained input cells, on Procore, Sage 100 Contractor and Outbuild.

A sensible way to start is small: Procore plus your accounting system, the WIP schedule, the exceptions page and month-end snapshots. Cash forecast, capacity and CRM pipeline can be added once the core numbers are trusted.

Where to go next

• Watch the build: Procore, QuickBooks and HubSpot into one WIP dashboard
• See every report page and metric definition: Procore, QuickBooks and HubSpot reporting
• Read the playbook: Replacing the monthly Excel report
• Still reconciling WIP by hand every month? Tell us what you're working with and we'll show you what an automated version would look like on your systems.