Building a Power BI Budget vs. Actual Forecast with EAC

A budget report can look reasonable and still answer the wrong question. The common cause is treating budget and actual transactions as if they were rows in the same fact table. Budgets are often approved by month, project and cost category; actuals may arrive by invoice line, labor entry or journal posting. Append those records into one amount column and a total can quietly combine a plan with spending that already happened.

A reliable model keeps the two grains clear, connects them through shared dimensions, and defines the forecast explicitly. That gives project leaders a useful view of approved budget, spend to date, expected finish and the size of any overrun.

Model budget and actuals at their own grain

Use separate fact tables such as Budget and Actuals. A budget row might represent one project, cost category and fiscal month. An actual row might represent one posted transaction, with a posting date, project, category and amount. Do not add budget and actual rows together just to make a single Amount field: summing that field would mix two different business meanings.

Create shared dimensions for Date, Project and Cost Category, with one-to-many relationships from each dimension to both fact tables. Keep dimensions on the one side, use single-direction filtering where possible, and avoid fact-to-fact relationships. If the budget is monthly while actuals are daily, relate both to the date dimension at a consistent key: use a month-start date for the budget and the calendar date for transactions, or introduce a month dimension that filters both. Make sure the date table covers the entire budget and forecast horizon.

This arrangement lets a project or category selection filter both facts without pretending they have the same granularity. If the budget is only approved at project level, do not allocate it across cost categories unless the business has agreed to a defensible allocation rule.

Start with clear base measures

Assuming positive amounts represent spending and budget values are stored as positive currency amounts:

Budget Amount =
SUM ( Budget[BudgetAmount] )

Actual Amount =
SUM ( Actuals[Amount] )

Budget Variance =
[Budget Amount] - [Actual Amount]

Variance % =
DIVIDE ( [Budget Variance], [Budget Amount] )

Here, a positive variance means budget remains; a negative value means actual spending has exceeded budget. Some organizations define variance as actual minus budget, so agree on the sign convention and label it in the report. Format currency and percentage measures explicitly, and use DIVIDE so a missing or zero budget does not cause an error.

Build a forecast that explains its assumptions

A forecast should combine actual results through the selected cutoff with expected costs after it. If a controlled monthly forecast table exists, use it as the source of future periods and clearly distinguish forecast from actual. Avoid summing both for the same month. For a simple run-rate estimate, estimate remaining cost from elapsed time and recent spend, but label it as a projection, not an approved forecast.

A basic estimate at completion (EAC) is actual cost to date plus the forecast cost to finish:

Forecast to Complete =
SUM ( Forecast[ForecastAmount] )

EAC =
[Actual Amount] + [Forecast to Complete]

EAC Variance =
[Budget Amount] - [EAC]

EAC Variance % =
DIVIDE ( [EAC Variance], [Budget Amount] )

This assumes the forecast table contains only periods after the actual cutoff and follows the same project and cost definitions. If it contains a full-year forecast, split actual-to-date and remaining forecast carefully so the EAC does not count the same period twice. For earned value projects, other EAC formulas may use cost and schedule performance indices; state which method the report uses.

Track burn rate with a defined period

Burn rate is spend per unit of time, so its meaning depends on the window. For a trailing three-month monthly average:

Monthly Burn Rate (3M) =
DIVIDE (
    CALCULATE (
        [Actual Amount],
        DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -3, MONTH )
    ),
    3
)

A straight-line months-to-exhaustion estimate can divide remaining budget by burn rate, but it is only useful when spending is reasonably steady and the remaining amount is positive. Projects with milestones, seasonality or one-time purchases need a forecast based on the delivery plan instead.

Design the page around decisions

Put approved budget, actual to date, variance percentage and EAC in the top KPI row. Add a monthly trend that separates actuals, remaining forecast and budget; a project or cost-category ranking of the largest EAC overruns; and a burn-rate view with its time window visible. Use consistent colors and label negative EAC variance as projected over budget.

Before delivery, reconcile a selected project and month to the source ledger and approved budget. Check totals at project, category and month levels, test blank and zero-budget cases, and confirm that changing the date range does not remove the future periods needed for the forecast. A chart is only trustworthy when the definitions, cutoff date and source reconciliation are visible to the people using it.