Creating Statistical Forecasts in Fabric IQ Planning - What Actually Works

Most Australian finance teams I talk to still build their forecasts the same way they did in 2010. Someone exports last year's actuals into Excel, applies a growth percentage, adjusts a few lines by gut feel, and emails it around for sign-off. It works, sort of. The problem is that it's slow, it bakes last year's anomalies into next year's numbers, and nobody can explain afterwards why a particular month was set where it was.

Statistical forecasting in Fabric IQ planning is Microsoft's attempt to give those teams a better baseline without asking them to become data scientists. I wrote earlier this month about the full range of forecasting options in Fabric IQ planning, and the statistical option was the one that generated the most questions. So this post goes further into that one feature: how to set it up, what to feed it, and where you should and shouldn't trust it.

The Microsoft reference is Create statistical forecasts in the Fabric IQ docs. Read that for the exact clicks. This post is about the judgement calls around them.

What a statistical forecast is (and isn't)

In Fabric IQ planning, a statistical forecast takes historical values from your planning sheet and projects them forward using time series methods. It looks at trend and seasonality in the history and produces values for future periods. You choose which measure to forecast, how much history to use, and how far ahead to project.

What it isn't: a driver-based model. It doesn't know that you're opening two new stores in Brisbane next quarter, or that your biggest customer's contract renews in March. It only knows the pattern in the numbers you give it. That's both its strength and its main limitation.

I find it helps to explain it to finance leads like this. The statistical forecast gives you a reasonable, defensible "if nothing changes" line. Your planners then adjust from that line for the things they know that the history doesn't. That's a much better conversation than starting from a blank sheet or from last year plus 5%.

Getting the data right first

Nine out of ten problems we see with statistical forecasts come down to the input data, not the forecasting method. Before you click anything, check these:

Enough history. Seasonal patterns need at least two full cycles to detect reliably. For monthly data with annual seasonality, that means 24 months minimum, and 36 is better. If you've only got 14 months of clean data because you changed ERPs last year, the forecast won't be able to tell seasonality from noise. It'll still produce numbers. They just won't mean much.

Consistent granularity. If some products are recorded monthly and others weekly, or if a cost centre was split halfway through the history, sort that out in the semantic model before forecasting. Fabric IQ planning works off your Power BI semantic model, so data quality issues upstream flow straight through.

Anomalies. COVID years are the obvious one. A hospitality client of ours had 2020 and 2021 revenue that looked nothing like a normal year, and including them in the history dragged the forecast into nonsense. Either exclude those periods from the history window or adjust the actuals into a cleaned version before using them as the forecast base. The same goes for one-off events like a large asset sale or a product recall.

Zeros and blanks. A product line that only started 8 months ago has 16 months of zeros before it in a 24-month window. The forecast will treat those zeros as real history. You're usually better off forecasting new lines separately, or manually.

Setting up the forecast

The basic flow in a planning sheet is to select the measure and scope you want forecast, choose the statistical forecasting option, and configure the parameters. The settings you'll care about most:

• The historical range. Which periods to learn from. This is where you exclude bad years.
• The forecast horizon. How many periods ahead to project. Keep this sensible. Statistical forecasts degrade the further out you go, and an 18-month projection off 24 months of history is stretching it.
• The target. Where the forecast values land, typically a forecast version or scenario column, so you're not overwriting actuals or an approved budget.
• Seasonality. Whether the method should look for a repeating pattern, and over what cycle.

My strong advice is to write forecast output to its own version, separate from the version planners will edit. Call it something like "Statistical Baseline FY27". Then create the working forecast as a copy that planners adjust. That way you can always compare where the planners ended up against what the statistics said, which is one of the most useful bits of analysis you can do at year end.

Reviewing the output before anyone relies on it

Don't publish a statistical forecast to the business without someone eyeballing it first. Here's what I look for:

Does the shape look right? Plot the history and the forecast together. If your business has a big December and a quiet January, the forecast should too. If it's a flat line, the method didn't pick up seasonality, probably because of insufficient history or noisy data.

Are there negative values? Some time series methods will happily forecast negative revenue for a declining product. That's mathematically fine and commercially absurd. Catch it and fix it.

How does it compare to last year? A forecast that's 40% up on last year with no business reason behind it should make you suspicious. Usually it means a trend got extrapolated from a short burst of growth.

Does it reconcile at the top? If you forecast at a detailed level (say product by region) and then sum up, the total might not match a forecast done at the total level. That's normal with statistical methods, but you need to decide which level you trust and reconcile the other one. In our experience forecasting at a mid level, like product category by state, then allocating down is often more stable than forecasting thousands of individual SKU and store combinations.

Where statistical forecasts work well

From the projects we've done, these are the sweet spots:

• High-volume, stable lines. Revenue for established product categories, recurring operating costs, utilities, standard headcount costs. Plenty of history, steady patterns.
• Seasonal businesses. Retail, agriculture inputs, tourism. Seasonality is precisely what these methods are good at picking up, and it's what humans often get wrong when they apply a flat growth rate.
• Rolling forecasts. If you reforecast monthly or quarterly, a statistical baseline that refreshes as new actuals land saves a lot of manual effort. Planners only touch the lines where they know something the history doesn't.

Where they don't

• Lumpy or project-based revenue. Professional services firms, construction, anything where a few big contracts drive the result. The history is too irregular, and the future depends on a pipeline the forecast can't see.
• Anything with a known structural change. New pricing, a merger, a lost major customer. The history describes a business that no longer exists.
• Small numbers. A cost line that bounces between $200 and $3,000 a month randomly won't forecast meaningfully. Just set it manually.

Honest assessment of the current state

Fabric IQ planning is still young, and statistical forecasting reflects that. The core capability is useful. The thing it does well, giving you a decent baseline quickly inside the same tool where planners do their work, is something that used to need a separate forecasting tool or a data scientist with a Python notebook.

But there are rough edges. You get less control over the underlying model than you would in a dedicated forecasting tool or a custom model in a Fabric notebook. Accuracy metrics and explanations of why the forecast landed where it did are thinner than finance teams want, and finance people quite reasonably don't trust numbers they can't explain to the CFO. Performance on large planning sheets with lots of dimensions can also be slow, so test at realistic scale before rolling out.

For organisations that need more sophisticated forecasting, like demand forecasting with external drivers such as weather, promotions or economic indicators, the better approach is often to build that model in Fabric with proper data science tooling, then write the results into the planning model as a baseline version. You get a better model, and planners still work in the familiar planning interface. We've built this pattern for a couple of clients and it's worked nicely. If that's where you're heading, our machine learning team does exactly this kind of work.

Getting started

If you're already using Fabric IQ planning, try a statistical forecast on one well-behaved measure with three years of clean history. Compare it to what your team produced manually last cycle. That comparison will tell you more than any vendor demo.

If you're still working out whether Fabric IQ planning fits your finance function at all, or you need help getting the underlying semantic model into shape so forecasts are worth running, our Microsoft Fabric consultants can help you work through it.