Configure Forecasts in Fabric IQ Planning - Baselines Zero-Based and Statistical

Every budget cycle we have worked on in the last few years starts the same way. Someone exports last year's actuals to a spreadsheet, adds a percentage uplift, emails it to six department heads, and waits three weeks for versions to come back in different shapes. By the time the numbers reach the board, nobody is certain which version is current, and the forecast is already out of date.

A planning tool that keeps the forecast inside the model, with the history, the assumptions and the locked numbers in one place, solves a lot of that. Microsoft Fabric IQ planning sheets are one of the newer options for this, and the forecast feature in them covers more ground than most people expect. This post covers how to configure forecasts, which methods we have found useful, and where we think you should be cautious.

The prerequisite most people miss

Before you touch the Forecast button, check your column dimension. The planning sheet needs a standard date hierarchy in the columns, such as Year, then Quarter, then Month. If the columns are a flat list of dates or a custom grouping, the forecast periods will not behave properly, and you will spend an afternoon wondering why the open periods are in the wrong place.

It sounds like a small thing, but the symptoms look like a forecast bug, so it eats the first day of a rollout.

Forecasting from historical actuals

The most common starting point is to use last year's actuals as the baseline for next year's forecast. Here is how it works in practice.

In the Model ribbon, select Forecast. The default measure name is Forecast, which you should change to something meaningful, such as Budget 2026 or Revised Forecast Q3. Then select the future period you want to forecast. The plan fills the forecast period based on the data you already have, which saves a step, but check it anyway.

Next comes closed periods. Closed periods are the past months where actual data exists. Forecast values for those periods are locked and cannot be edited, which is the behaviour you want. To populate them with real numbers, select Link to Measure and choose the measure you want, typically Actuals.

Then the open periods, which are the ones you are actually forecasting. For a manual approach, select Data Input and leave the Default Value as None. To prefill those periods from history, expand the Pre-fill Open Periods section, set Copy from to the measure with the source values, set Operation to Period Range, and define the source and target ranges. In the example Microsoft uses, the 2026 Budget is initialised from the 2025 Actuals. Planning copies each month across to the matching month in the target range.

My honest view on prefilling from last year: it is a sensible starting point and a poor finishing point. Last year's numbers include whatever happened last year, including the one-off contract that landed in March, the restructure in the middle of the year, and the expense that finance now considers a mistake. A prefill gives you a number to argue with, which is useful, but it should not be the number you finish with. We usually tell clients to treat the prefill as scaffolding and remove the parts that do not reflect next year.

Zero-based forecasts

A zero-based forecast starts from nothing. You create a second forecast measure, add it through the Measures pane, follow the same steps for the timeframe and closed periods, and make sure the open period is set to Data Input. The open periods start at zero, and each budget holder enters what they expect to spend or earn based on current assumptions.

We like zero-based forecasts for the conversation they force. A prefilled budget can make people lazy. They look at the number, think it is about right, and nudge it up or down by a few percent. A zero-based forecast asks them to justify every line. It is slower and generates more pushback, which is exactly the point.

The downside is that it is harder work for the business, and it can stall if the people filling it in do not understand the drivers. Our recommendation is to run a workshop before the zero-based version goes out, walk through the assumptions and the drivers, and agree on the inputs that matter most. Then let people fill in the detail.

A useful pattern that we have seen work well is to keep both measures in the sheet. The prefilled budget from actuals shows the baseline, the zero-based forecast shows the bottom-up view, and the difference between them is the conversation you need to have.

Deviation between budget and forecast

Once you have a budget measure and a forecast measure, the next thing people ask for is the gap between them. In the Planning ribbon, select Forecast and set the measure name to something like Deviation. Keep the default forecast period for the year, then in the closed period configuration, select Formula and enter the expression that compares budget and forecast. Use the same configuration for the open periods and save.

The Microsoft article does not specify a sign convention for the deviation, so decide that before you build the measure and write it down. Whether a positive number means over budget or under budget depends on whether the line is revenue or cost, and getting that wrong is an easy way to confuse a board. We have seen two different departments in the same organisation use opposite conventions and then argue about who was winning.

Top-down and bottom-up allocation

Allocation is where planning sheets earn their keep, and also where you can get into trouble quickly.

Top-down allocation starts with a high-level number and spreads it to the child rows. Before you do this, change the scaling in the Planning ribbon to None, so you see the full value rather than a rounded thousand. Double-click the grand total cell for the measure you want, enter the new value, and the figure is allocated to the child nodes. As the Microsoft documentation describes it, the allocation follows the self weight of each child, so the split depends on the weights already in the sheet.

Check the split before you trust it. If the weights are all equal, your top-down number will be spread evenly across lines that may have very different sizes. That is rarely what anyone wants, and it is easy to miss when the grand total looks right.

Bottom-up allocation goes the other way. Enable subtotals before you start by setting Column Subtotal to Left, which brings up the 2026 total column on the left. Then select a leaf node or a subtotal and enter the value. The number aggregates up to the grand total.

Our practical advice: decide which level owns each number. If a department head owns the leaf lines and the finance team owns the top-down total, make that explicit in the process. If both can overwrite each other, the last person to save wins, and that is a bad way to run a budget.

Locking approved numbers

Once a forecast is approved, you need to stop people changing it. In the Model ribbon, go to Rules and then Locking Rule. Set Apply to Measures to Selected Measures, choose the measure you want to lock, and select Apply to Children so that child rows cannot be edited either. Select Create. The locked cells will show as locked in the grid.

Before you lock anything, save a copy of the sheet or create a version measure with the approved numbers. Locking is easy to reverse for an administrator, but it is annoying when someone locks the wrong measure in the middle of a review and the team cannot work out why their edits have stopped. We would rather have a clean record of what was approved than rely on the lock to be the record.

Statistical forecasts with Predict

The Predict feature generates a forecast from historical data, using statistical models to find trends and seasonality. You do not have to type in a single assumption, which is both its attraction and the reason to be careful with it.

To use it, first set the Model ribbon's Period option so the historical periods are visible. Hide closed forecasts so you can focus on the history and the open periods. Select the row you want to forecast. In the example Microsoft uses, that is the grand total for a Forecast measure. Select Predict, set Evaluation to Bottom Up, and the forecast is generated from the trend on each leaf node. In that example the leaves are the chart of accounts.

The Trend Decomposition algorithm is selected by default. Set Seasonality to Year, if your data has an annual pattern, and select Run Forecast. You will see a graph of the predicted values, then a table with the actual predicted values and the confidence interval for each period. When you are happy with it, select Save Forecast, review the measure and period, and save.

Some honest observations:

• The confidence interval is more useful than the point estimate. A forecast of 4.2 million with a range of 3.1 to 5.4 million is telling you something important about how much to trust the number. Show the range to the people using it, not just the middle.
• Bottom-up evaluation is sensible when your leaves behave differently, but one volatile leaf with a single large historical spike can distort the trend for the parent. Look at the individual lines that drive the result before you accept it.
• You need enough history for seasonality to mean anything. A year of data gives you a rough pattern. Two or more years gives the model a fairer chance. If you only have a few months, treat the output as a rough guide.
• Statistical forecasts are a starting point for a conversation, not a replacement for one. The model does not know that a major customer is leaving in March or that a price rise is coming in July. Put those judgements on top of the statistical baseline, and record what you changed and why.

Where it is still rough

There are a few things we would flag before you commit a full budget cycle to this approach.

The interface has a lot of options that look similar, and it is easy to build a forecast measure with the wrong closed or open period configuration and not notice until the numbers look strange. We recommend building the first forecast on a copy of the production sheet, with a small set of rows, and checking each step before you scale it up.

The relationship between measures, periods and allocation can get complicated fast. A sheet with four forecast measures, two deviation measures, and a mix of top-down and bottom-up entry is hard to explain to a new user. Keep the design simple where you can, and document which measure is the source of truth for each figure.

Where to go from here

If you are planning a Fabric IQ rollout and want help designing the forecast structure, the closed and open period rules, or the approval process around it, our Microsoft Fabric consultants have worked through these set-ups with finance teams. For the reporting and analysis layer that sits on top of the planning data, our AI for business intelligence team can help you decide what should be in the model and what should stay in the report.

The Microsoft article that this post draws on is Create forecasts using predictions and historical data on Microsoft Learn. Read it alongside a test sheet, and you will have a much better sense of which parts of the process suit your organisation.