Fabric Planning: Build a P&L Forecast

This session builds a country-level P&L forecast from scratch on an existing semantic model, working through nine user stories a CFO might write. It covers locking a budget, forecasting, tracking deviation, top-down and bottom-up allocation, assigning country managers, collaboration, scenarios, audit, export and writeback.

Key takeaways

1. Lay it out the way you would in Power BI. Rows carry country, P&L category, subcategory and GL account. Columns carry the date hierarchy down to month. Values come from a DAX measure in the semantic model, which gives you actuals before you've typed a number.
2. Build the budget and lock it. A forecast measure named Budget copies actuals into the closed periods, which lock automatically, and seeds 2026 from last year. A top-down change to net income spreads by weight to every country and GL account. A lock rule then freezes the budget at every level until the rule is removed.
3. Let Predict draft the forecast. Predict runs on the actuals history. With bottom-up evaluation, each GL account follows its own trend and seasonality, multi-seasonal in this case, and the results show confidence ranges. Accounts that are negative stay negative in the forecast.
4. Show deviation without leaving the grid. A formula measure gives forecast against budget. Conditional formatting adds font colours, icons and data bars, so outliers stand out at a glance.
5. Bottom-up edits and ownership. Lock a row you don't want touched, such as purchases, and change the rest. Totals update across every period. A person column assigns a country manager to just the United States rows, notes record context, and an @mention in a comment sends that person an email.
6. Run scenarios and promote one. Upside, downside and balanced scenarios were built on the forecast measure only. Copy to base made the balanced scenario the new forecast, with no staging models.
7. Write back to Fabric SQL with checks. Choose long or wide format, with or without change tracking, and pick only the measures you need (here, budget and forecast). Validation rules can stop a writeback with blank values or bad column names, and every run is logged. The audit trail shows build and data-entry changes by user, and the sheet exports to Excel or PDF with formatting kept.

FAQ

• Are scenarios written back to separate tables?
  No. They go to one table with a scenario column, so each row says whether it's base, upside, downside or balanced.
• Once the plan is written back, how do I report on it in Power BI?
  If your semantic model is built on the same Fabric SQL database, open the model in edit mode, add the newly written-back table and set its relationships. Many customers do exactly this.
• Is the ValQ driver-based simulation tree still part of the product?
  Yes. Driver-based planning is the row model, built in Model Builder from the Model tab, and Tree View gives you the simulation tree.
• Can each country have its own manager with access to only their country?
  Yes. Row-level security in the semantic model is respected. On top of that, the plan's own security controls who can work with each measure, create scenarios or write back.
• Do I need a date dimension to forecast?
  Yes. A forecast is a time extension of a measure, so the sheet needs a date dimension in the columns.
• Can we import the formulas from an Excel financial model?
  Not on the roadmap. The formula engine uses Excel-compatible functions and syntax, so it feels familiar, but a formula is written once for a whole measure rather than copied cell by cell.

Resources

• Capacity estimator: https://community.fabricplan.com/capacity-pricing/
• Related webinar, Build Statistical Forecasts using ETS and ARIMA models (Jul 28): https://community.fabricplan.com/webinars/fabric-planning-build-statistical-forecasts-using-ets-and-arima-models/
• Related webinar, Three Ways to Create Planning Models in Microsoft Fabric (Aug 25): https://community.fabricplan.com/webinars/fabric-planning-three-ways-to-create-planning-models-in-microsoft-fabric/
• Documentation: https://community.fabricplan.com/docs/ · Writeback: https://community.fabricplan.com/docs/planning-sheets/how-tos/collabration-and-export/persist-planning-data-using-writeback/
• Ask a question: https://community.fabricplan.com/forums/