How to Build a Construction WIP Report in Power BI

The construction WIP report is the most important report a contractor produces and the one most often assembled by hand in Excel every month. Moving it into Power BI is achievable, but only if the accounting is settled first. Proxsoft Global builds construction WIP reporting in Power BI for U.S. contractors, and the failures are always definitional rather than technical.

Ashwini Jain

August 21, 2026

What the WIP schedule actually calculates

A construction WIP report compares what you have earned on a contract to what you have billed, and recognizes the difference. Every row on the schedule comes from the same five inputs:

Input

Source

Common problem

Contract value

ERP job master plus approved change orders

Pending change orders included inconsistently

Cost to date

Job cost actuals

Accrued versus posted cost mixed across jobs

Estimated cost at completion

PM forecast

Not updated, so percent complete is wrong

Billed to date

AR and pay applications

Retainage billed but not received treated inconsistently

Percent complete

Cost to date divided by estimated cost at completion

Overridden manually with no audit trail

From those five: earned revenue equals percent complete multiplied by contract value. Overbilling is billed to date minus earned revenue when positive. Underbilling is earned revenue minus billed to date when positive. That is the whole calculation. The difficulty is never the math.

Step 1: settle the definitions before opening Power BI

Every construction WIP report that disagrees with the ledger fails on one of these. Get written agreement from the controller:

Do pending change orders count in contract value? Most contractors say no for revenue recognition and yes for forecasting, which means two measures, not one.

Is cost to date posted or accrued? If accrued, which accruals, and are they reversed consistently?

Is percent complete always cost-based, or are some job types units-based or milestone-based?

How is retainage receivable presented, and is it inside or outside billed to date?

What is the cutoff? A WIP built on a different cutoff than the trial balance will never tie.

Step 2: build the data model

Do not point Power BI for Construction at ERP tables and write the whole construction WIP report in one enormous query. Build a proper model:

A Jobs dimension: job number, name, division, PM, job type, status

A Date dimension covering fiscal periods as your ERP defines them, not calendar months

A Job Cost fact table at job, cost code and period grain

A Billing fact table at job and period grain, including retainage

A Forecast table holding estimated cost at completion by job and period, with history retained

That last table is the one contractors skip, and it is why most Power BI WIP reports cannot show a prior-period comparison. Snapshot the forecast every period. Without history you cannot show margin movement, which is the single most useful column on the schedule.

Step 3: write the measures

Keep them explicit and keep them few. A construction WIP report needs roughly ten measures, not fifty.

Contract Value = original contract plus approved change orders

Cost to Date = sum of job cost actuals through the selected period

Estimated Cost at Completion = forecast for the selected period

Percent Complete = Cost to Date divided by Estimated Cost at Completion

Earned Revenue = Percent Complete multiplied by Contract Value

Overbilling = MAX(0, Billed to Date minus Earned Revenue)

Underbilling = MAX(0, Earned Revenue minus Billed to Date)

Estimated Gross Profit = Contract Value minus Estimated Cost at Completion

Margin Movement = current period Gross Profit Percent minus prior period

Step 4: lay out the schedule the way finance reads it

Controllers and sureties expect a specific column order. Do not redesign it. One row per job, columns left to right: job number, job name, contract value, estimated cost at completion, estimated gross profit, gross profit percent, cost to date, percent complete, earned revenue, billed to date, overbilled, underbilled, margin movement. Totals at the bottom that tie to the balance sheet. Add drill-through to job cost detail. The first question after any surprise on the schedule is which cost code moved, and answering it in the same report is most of the value of construction WIP reporting in Power BI over a spreadsheet.

The two mistakes that break every WIP rebuild

First, forecast data with no history. If estimated cost at completion is read live from the ERP, last month’s schedule changes every time you open it, and you can never reproduce what you reported. Snapshot it.

Second, a percent complete that is silently capped or overridden. Jobs go over 100 percent complete when the forecast is stale. Show it rather than capping it, and flag those jobs, because an uncapped construction WIP report that looks wrong is telling you the forecast is wrong.

What you gain over Excel

Prior-period comparison and margin movement without rebuilding the file

Drill-through from any number to the transactions behind it

Division, PM and job-type views from the same model

A schedule that reconciles to the ledger every period because both read the same source

Time back at close. Most contractors recover two to four days of the monthly cycle

Frequently Asked Questions

How do I build a construction WIP report in Power BI?

Settle the accounting definitions first, then build a data model with Jobs, Date, Job Cost, Billing and a Forecast snapshot table. Write about ten explicit measures covering contract value, cost to date, percent complete, earned revenue, over and under billing and margin movement. Lay the report out in the standard WIP column order and add drill-through to job cost detail.

Why does my Power BI WIP report not tie to the general ledger?

Usually one of three reasons: the period cutoff differs from the trial balance, cost to date mixes posted and accrued amounts, or pending change orders are included in contract value in one place and not the other. Reconcile a single job line by line against the ledger before trusting the totals.

What data do I need for a construction WIP report?

Five inputs: contract value including approved change orders, cost to date, estimated cost at completion, billed to date including retainage treatment, and percent complete. All five normally come from the ERP, except the forecast, which usually comes from project managers and must be snapshotted each period.

Should percent complete be capped at 100 percent in a WIP report?

No. Capping hides the problem. A job showing over 100 percent complete means the estimated cost at completion is stale, and that is exactly what the schedule should surface. Flag those jobs for forecast review instead of masking them.

Can Power BI replace our Excel WIP schedule?

Yes, and it typically saves two to four days of the monthly close. The requirement is a snapshotted forecast table so prior periods remain reproducible. Without forecast history, a Power BI WIP report cannot show margin movement and last month’s schedule will change every time it is opened.