Build a Power BI report that compares saved forecasts with actual results at a fixed forecast horizon. Prepare the workbook, calculate error and bias measures, reconcile the totals, and identify recurring misses by business segment.
Forecast accuracy in Power BI starts with a saved forecast and a matching actual result, measured at a fixed horizon. Calculate the error on each matched row, then summarize those errors by period and segment. Keep signed bias beside absolute error so opposite misses cannot disappear inside a company total.
This workflow uses an existing revenue forecast and closed actuals from a US distributor. It builds a report for reviewing planning performance, with the original forecast preserved.
Choose one forecast rule: the last approved snapshot before the forecast month begins. August 2026 therefore uses the July 31, 2026 snapshot; September uses the August 31, 2026 snapshot. Both are one-month-ahead forecasts under this convention.
Save the snapshot date with every row. Do not replace it when the sales team revises its outlook during the month. Otherwise the report measures how closely the latest revision followed actuals rather than how useful the original forecast was for planning.
Use the same revenue definition on both sides: net revenue in USD, for the same closed month and regional assignment. The forecast owner confirms the snapshot; finance confirms the actuals and reporting cutoff.
Start with the forecast export and the closed revenue export. Aggregate both to period and region, then match those keys in a workbook. Retain regions present in only one export for investigation; do not silently drop them.
Use these columns: A Period, B Snapshot, C Region, D Actual, E Forecast, F SignedError, and G AbsoluteError. Store Period as a month-start date, displayed as month and year. The table below shows the matched rows and the error columns you will calculate.
| Period | Snapshot | Region | Actual (USD) | Forecast (USD) | SignedError (USD) | AbsoluteError (USD) |
|---|---|---|---|---|---|---|
| August 2026 | July 31, 2026 | East | 100,000 | 110,000 | 10,000 | 10,000 |
| August 2026 | July 31, 2026 | West | 80,000 | 72,000 | -8,000 | 8,000 |
| September 2026 | August 31, 2026 | East | 120,000 | 132,000 | 12,000 | 12,000 |
| September 2026 | August 31, 2026 | West | 100,000 | 96,000 | -4,000 | 4,000 |
| Total | 400,000 | 410,000 | 10,000 | 34,000 |
In Excel, enter the following in F2 and G2, then fill down through row 5:
=IF(COUNT(D2:E2)<>2,"",E2-D2)
=IF(F2="","",ABS(F2))
The sign convention is forecast minus actual: positive means overforecasting. Check numeric completeness in H2:
=IF(COUNT(D2:E2)=2,"Matched","Check")
Investigate every Check row before importing the comparison table. A confirmed zero is numeric; an unavailable actual remains blank. Do not turn missing results into zero to make the formulas run.
Check uniqueness in I2, filled down:
=COUNTIFS($A$2:$A$5,A2,$B$2:$B$5,B2,$C$2:$C$5,C2)
Each result must be 1. Expand the ranges for your full export. For the complete spreadsheet review process, use the Excel guide to measuring forecast accuracy and bias.
Name the prepared workbook table ForecastActual and save it after calculation. In Power BI Desktop, select Home > Get data > Excel, choose the workbook and table in Navigator, then select Load. These are the steps in Microsoft's Excel connection quickstart.
Check that Period and Snapshot are dates and the amount columns are numeric. Load the detail rows only; including the workbook's Total row would count the amounts twice.
Create each expression below as a separate measure through Modeling > New measure. Microsoft documents that measures use DAX and recalculate as readers interact with reports.
Actual Revenue = SUM(ForecastActual[Actual])
Forecast Revenue = SUM(ForecastActual[Forecast])
Signed Error = SUM(ForecastActual[SignedError])
Absolute Error = SUM(ForecastActual[AbsoluteError])
WAPE = DIVIDE([Absolute Error], [Actual Revenue])
Bias Percent = DIVIDE([Signed Error], [Actual Revenue])
Format the revenue and error measures as USD, and WAPE and Bias Percent as percentages. These measures assume nonnegative actual revenue. Separate credits or reversals that would make the denominator misleading.
With no regional filter, WAPE is 34,000 / 400,000 = 8.5%. Bias is 10,000 / 400,000 = 2.5%. If your organization reports accuracy as one minus WAPE, that convention gives 91.5%; label the convention rather than presenting it as a universal accuracy definition. The WAPE formula guide covers that metric's interpretation.
The DAX DIVIDE function returns blank for a zero denominator when no alternate result is supplied. Keep that behavior: a group with no actual revenue has no usable percentage denominator.
Put actual revenue, forecast revenue, absolute error, WAPE, and bias at the top. Under those measures, create a matrix with Region as rows and the same measures as values. Add a period trend and a detail table containing the snapshot, period, region, actual, forecast, and errors.
Add visible period and region slicers. Microsoft describes slicers as report controls that filter other visuals; verify that your headline figures, matrix, and detail table all respond to the intended selections. Keep the fixed horizon in the report title.
The regional view must reproduce these results, with percentages rounded to one decimal place:
| Region | Actual (USD) | Forecast (USD) | Signed error (USD) | Absolute error (USD) | WAPE | Bias |
|---|---|---|---|---|---|---|
| East | 220,000 | 242,000 | 22,000 | 22,000 | 10.0% | 10.0% |
| West | 180,000 | 168,000 | -12,000 | 12,000 | 6.7% | -6.7% |
| Total | 400,000 | 410,000 | 10,000 | 34,000 | 8.5% | 2.5% |
East overforecasts in both months; West underforecasts in both. That pattern directs the review toward regional assumptions, booking timing, customer changes, or missing orders. The rows establish the pattern; the underlying sales records must establish its cause.
For error in dollars per observation, use MAD forecast accuracy. A Power BI cash flow forecast addresses a different decision: when forecast activity turns into cash.
Tie actual revenue to the closed revenue report and forecast revenue to the archived approved snapshot. Then confirm that 410,000 - 400,000 = 10,000, equal to summed signed error.
The absolute-error check is different: 10,000 + 8,000 + 12,000 + 4,000 = 34,000. Taking the absolute value of the net company miss would return only $10,000 and hide the offsetting regional errors. Select each region and confirm the report reproduces the table above before distributing it.
Covirage is not affiliated with Microsoft.
Bring the saved forecast, actuals, and supporting regional detail as a file or Google Sheet: Covirage maps the columns, its tools compute and check the figures, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring a sample of your own data.
See forecast analysis for the supporting business workflow.
A saved business forecast needs its own comparison with actual results. Keep the original forecast amount and snapshot date, match each row to the corresponding actual period and segment, and calculate errors from those pairs. A later revised forecast answers a different question.
For an overall WAPE measure, divide summed row-level absolute errors by summed actual amounts. Averaging segment percentages gives each segment equal weight regardless of size. Keep the chosen aggregation rule visible so regional totals and company totals describe the same calculation.
Keep a confirmed zero distinct from missing actuals. A forecast against a genuine zero still contributes an absolute error. If the selected group's total actual amount is zero, show its percentage error as unavailable and retain the absolute error for review.
Use the horizon that matches the business decision. A forecast saved before the month starts can assess monthly planning, while an earlier snapshot may assess purchasing commitments. Compare like horizons and label the selected snapshot rule directly on the report.