Power BI demand forecasting: compare plans with actuals

Power BI demand forecasting compares a forecast saved before the sales period with actual product demand recorded afterward. Start with matching SKU, location, unit and period definitions, then show forecast, actual and error together. The useful result is a review list the demand planner can act on, supported by totals that agree with the exports.

This workflow evaluates an existing planning file. For the broader definition and planning choices, see demand forecasting.

Start with the plan the business approved

Export the approved forecast and the sales register for a completed month. Preserve the forecast approval date, version, product code, location or channel, demand month and quantity unit. Actual sales need the same business keys plus the source record identifier and the event date used to assign the month.

Use one comparison row per SKU, location and month after aggregating sales. Several sales lines can belong to one forecast row; the forecast must appear only once in that comparison. Keep unmatched products visible until someone explains them. A discontinued SKU can legitimately have actual sales without a new forecast, while a missing product mapping needs correction.

Freeze the comparison set before calculating error. If a forecast was updated during the month, retain that revision separately. Replacing the original approval with hindsight makes the evaluation easier without improving the decision that production or purchasing faced.

Demand quantities also need a clear basis. Cases and individual units cannot share a total without conversion. Dollar forecasts belong in the separate Power BI revenue forecasting workflow, where selling price and revenue timing also matter.

Calculate the comparison in Excel first

Suppose a US distributor reviews September 2026 demand for three products in one distribution location. Every quantity below is in individual units. Its forecast snapshot was approved before September began, and the sales export contains completed September sales on the same basis.

Put SKU in column A, forecast in B and actual in C. Calculate signed error in D and absolute error in E:

D2 = B2-C2
E2 = ABS(D2)

Fill both formulas through row 4. A positive signed error means the plan exceeded actual demand; a negative value means it fell short.

If actuals come from a transaction sheet named Sales, with SKU in A, period label in B and units in C, populate C2 as follows. Put the same period label in H1 and use the complete source range in the real workbook.

=SUMIFS(Sales!$C$2:$C$1000,Sales!$A$2:$A$1000,A2,Sales!$B$2:$B$1000,$H$1)

Add location as another criterion when the file contains several locations. Microsoft's SUMIFS documentation confirms that the function sums rows meeting multiple criteria. A zero result still needs a completeness check: it can mean no sales or no matching records.

Read bias and absolute error together

Two totals answer different questions:

Signed bias percentage = Sum of forecast minus actual / Sum of actual

WAPE = Sum of absolute row errors / Sum of actual

Here signed bias is 40 / 2,260 = 1.77%, while weighted absolute percentage error, or WAPE, is 240 / 2,260 = 10.62%. Percentages are rounded to two decimal places; calculations retain the underlying values.

=IF(SUM(C2:C4)=0,"",SUM(D2:D4)/SUM(C2:C4))
=IF(SUM(C2:C4)=0,"",SUM(E2:E4)/SUM(C2:C4))

Products A and B have equal and opposite signed errors. They cancel in bias even though both need attention. The distinction matters when buyers can have excess stock of one item and shortages of another. Use forecast bias by SKU and site for the deeper review of persistent direction.

The arithmetic proof is straightforward: 2,260 actual units plus 40 signed error units equals 2,300 forecast units. Absolute errors sum separately to 240. Reconcile the forecast total to the saved approval and the actual total to the sales export before accepting either percentage.

Put the validated comparison into Power BI

Save the recalculated workbook with a header row, consistent date and number types, and no embedded subtotal rows. In Power BI Desktop, use Get Data > Excel to select the comparison table. Microsoft's Excel workbook guidance distinguishes this simple worksheet route from importing workbooks that contain more elaborate analytical structures.

Create a matrix with SKU as rows and the forecast, actual, signed error and absolute error columns as summed values. Add location and month to the row hierarchy when those fields exist. Microsoft's matrix visual instructions describe those row, column and value placements.

Confirm that the unfiltered matrix reproduces the worked totals before adding filters. Then select one product and compare its values with the workbook. Repeat for a location and a month in the real file. Leave ratio totals off the report until they reproduce the ratio of summed components under each filter; summing or averaging imported percentage cells will not do that.

Power BI also offers a forecast in the Analytics pane for line charts, according to Microsoft's forecasting guidance. Label any such projection separately from the supplied approved plan. A projection created during the review must not replace the snapshot being scored.

Covirage is not affiliated with Microsoft, the maker of Power BI and Excel.

Where it goes wrong

• Shipment constraints become demand conclusions. A stockout can suppress sales. Review availability or unfilled orders before reducing the next forecast.
• The same forecast repeats on sales lines. Aggregate actuals first so the plan is counted once for each comparison key.
• Periods change with the export. Requested delivery date, shipment date and invoice date can assign an order to different months.
• Only the net error gets attention. The small signed total hides the opposing errors in A and B. Keep absolute errors beside it.
• Promotion periods mix with ordinary sales. Tag promotions before explaining changes. CPG demand forecasting covers that separate channel and promotion review.

Have the demand planner record the explanation, the evidence and the next action for each material exception. The report identifies where the plan missed; stock availability, order changes and commercial evidence establish why. If you are comparing reporting approaches, the Power BI alternatives page covers that broader choice.

Review your own demand file in Covirage

Bring a sample of your own approved SKU forecast and comparable sales export, or a Google Sheet, to Covirage. Covirage maps the columns, its tools compute and check the comparison figures, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring the file you need to review.

See FP&A reporting for the related finance reporting context.