Budget vs Actual in Power BI: A Step-by-Step Guide

Power BI P&L summary comparing actual, budget and last year

Budget vs actual is the report every finance team asks for first. It looks simple: put the actual numbers next to the budget and show the difference. In practice, it is where many Power BI models go wrong, because budgets and actuals rarely arrive in the same shape.

In this guide I'll walk through the approach I use with clients: how to structure the data, which DAX measures you need, and how to present variances so managers can act on them.

Why budget vs actual is harder than it looks

Three things usually cause trouble:

  1. Different levels of detail. Actuals come from the general ledger as daily transactions. Budgets are usually monthly, by account and cost center.
  2. Different meanings of "good". Revenue above budget is good news. Costs above budget are not. A plain "actual minus budget" doesn't tell you which is which.
  3. Partial periods. In the middle of a month, comparing month-to-date actuals with a full month's budget makes every number look bad.

A good model handles all three, so the report answers the real question: where are we off plan, and does it matter?

Step 1: Keep budget and actual in separate tables

Don't merge the budget into the actuals table. Keep two fact tables and connect both to the same dimension tables:

  • Actuals: posting date, account, cost center, amount
  • Budget: month, account, cost center, amount
  • Shared dimensions: Date, Account (with report line and account type), Cost Center

This is a standard star schema. Because both fact tables share the same dimensions, a single slicer on cost center or account filters both at the same time.

Step 2: Connect the monthly budget to your Date table

Add a date column to the budget table that holds the first day of each month, then relate it to your Date table. The budget now filters correctly by month, quarter and year.

One caution: at day level, the whole month's budget sits on the 1st. For daily views, either keep budget visuals at month level or allocate the budget across days in Power Query.

Step 3: Write the core measures

Start with four simple measures. Everything else builds on them.

DAX
Actual = SUM ( Actuals[Amount] )

Budget = SUM ( Budget[Amount] )

Variance = [Actual] - [Budget]

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

Using ABS in the denominator keeps the sign of the percentage meaningful, even when the budget itself is negative.

Step 4: Show favorable and unfavorable correctly

For revenue, a positive variance is favorable. For expenses, it is unfavorable. Store the account type in your Account table and let the measure flip the sign where needed:

DAX
Variance Impact =
SUMX (
    VALUES ( Account[Account Type] ),
    VAR Sign = IF ( Account[Account Type] = "Expense", -1, 1 )
    RETURN ( [Actual] - [Budget] ) * Sign
)

Because the measure loops over account types, it also works on subtotals such as Operating Profit, where revenue and expenses are mixed. A positive Variance Impact is always good news, so you can color it green and a negative one red, with no exceptions to explain.

Step 5: Add year-to-date and full-year views

Managers want to see the month, the year so far, and how the year is expected to end.

DAX
Actual YTD = CALCULATE ( [Actual], DATESYTD ( 'Date'[Date] ) )

Budget YTD = CALCULATE ( [Budget], DATESYTD ( 'Date'[Date] ) )

Budget Full Year =
CALCULATE ( [Budget], REMOVEFILTERS ( 'Date' ), VALUES ( 'Date'[Year] ) )

If your financial year doesn't end in December, add the year-end date to DATESYTD, for example DATESYTD ( 'Date'[Date], "6/30" ) for a June year end.

Step 6: Compare only closed periods

To avoid comparing half a month of actuals with a full month of budget, limit the budget to the periods that have actuals:

DAX
Budget to Date =
VAR LastActualDate =
    CALCULATE ( MAX ( Actuals[Posting Date] ), REMOVEFILTERS () )
RETURN
    CALCULATE ( [Budget], KEEPFILTERS ( 'Date'[Date] <= LastActualDate ) )

Many finance teams go one step further and keep a small "Closed periods" table, so the report only moves forward once the month is officially closed.

Step 7: Design the report for decisions

A few layouts work well again and again:

  • A P&L matrix with Actual, Budget, Variance and Variance % side by side, using conditional formatting on Variance Impact
  • A variance waterfall that walks from budgeted profit to actual profit, line by line
  • Drill-down from company to department to cost center, so managers can find the cause of a variance in a few clicks
  • A short commentary box next to the numbers, because a variance without an explanation starts a meeting instead of ending one

Final thoughts

A budget vs actual report is only as good as the model behind it. Keep budget and actuals in separate tables, connect them through shared dimensions, and let a few well-written measures handle signs, periods and subtotals. The result is a report finance teams trust and use every month.

At Tecrionix, we build budget vs actual, P&L and forecasting dashboards for finance teams, and we teach these techniques in our Power BI Training program. To discuss your reporting requirements, contact us.

Looking to strengthen your Power BI reporting?

We build dashboards and train teams to use Power BI with confidence.