Compare a monthly budget with daily actuals in Power BI
Model monthly budget and daily transactions in separate tables, and keep actual-only, budget-only, and zero-budget rows in the report.
Compare at the month level and keep transaction dates
A marketing budget might have one row for April while the general ledger has many April transactions. Merging a monthly budget row directly onto each transaction repeats the budget and overstates the total. Keep the files as two fact tables with their original level of detail.
- In both tables, create a MonthStart date set to the first day of the relevant month. Derive it from the posting date for actuals.
- Make a Month lookup that has one row per month across both files. Relate it one-to-many to each table through MonthStart. Use shared Department and Account lookups built from identifiers found on either side.
- Add a matrix grouped by Department and Account from the lookup tables. Apply the period filter from the Month lookup. Add separate Budget and Actual measures before adding variance.
- Use posting date in the transaction detail. An April budget applies to the whole month. A daily planned value requires an explicit allocation rule, such as a business-day schedule.
Retain one-sided rows and distinguish zero from missing
Build matrix rows from the common lookups so a cost center or account can appear when it exists in either file. Filter out combinations only when both measures are blank. If a known account is absent, check the relationship keys and visual filters. A filter such as Budget greater than zero hides both actual-only rows and explicitly zero budgets.
Retain the amount that exists and label the other side as missing. Compare where both values exist. For an explicit zero budget, show the dollar variance and leave the percentage unavailable. DAX DIVIDE returns blank when the denominator is zero.
Use these rows to test the result
Open the sample for April 2026. Operations / Freight has $950 actual and no budget. Sales / Events has a $1,250 budget and no actual. Product / Tools has a $0 budget and $325 actual, so the dollar variance is $325 and the percentage is unavailable.
Add the actual and budget columns separately and reconcile each against its source. If you display departments with neither an actual nor a budget, the Show items with no data setting can retain those groups after the model relationships are in place.
Try this with the sample report
Use the same budget and transaction files to check the April Marketing results.
