4 - Create a rolling forecast

In this tutorial, you build a rolling 2026 forecast anchored on 24 months of actuals, generate a statistical forecast using the Trend Decomposition with MSTL algorithm, close January 2026 as actuals arrive, extend the horizon to January 2027, and commit the finalized forecast to a Fabric SQL database.

Prerequisites #

Complete the first three tutorials: Introduction to planning in Microsoft Fabric, Allocations and collaboration, and Optimize input values before starting this tutorial. The plan app and the Fabric SQL database created in the previous tutorials are used here.

Create a rolling forecast sheet #

In this section, you set up the forecast planning sheet and create a blank forecast measure for 2026, ready to be populated by the statistical model.

1. In Northwind_FMCG_Plan, select New Planning Sheet in the Home ribbon. Enter Forecast and select Create.
2. Configure the field assignments:

3. In the Filter panel in the sidebar, apply a year filter for 2024 and 2025. Only these two years’ values feed into the statistical forecast.
4. In the Planning ribbon, select Totals and enable Column Subtotal on the left.

5. In the Model ribbon, select Forecast.
6. Enter Forecast as the measure name. Set the Forecast Period to Jan 2026 to Dec 2026. Select Next.

7. In the closed period configuration, select Link to Measure and set Source Measure to Gross Revenue. This brings in the corresponding Gross Revenue actuals once a period closes. Select Next.

8. In the open period configuration, select Data Input and set Default Value to None. Leaving open periods blank allows the predict feature to populate the forecast using statistical methods. Select Save.

9. Observe that the 2024 and 2025 forecast columns appear greyed out, indicating closed periods. The 2026 columns are open and empty, ready for the statistical model.

Generate the statistical forecast #

In this section, you run the statistical forecast across the full Jan–Dec 2026 horizon using 24 months of historical actuals. The Trend Decomposition with MSTL algorithm detects seasonal patterns at both annual and quarterly cycles and generates a complete year of forecasted values in one pass.

1. Select the cell at the intersection of the All row and the Forecast subtotal column for 2026.
2. In the Model ribbon, select Predict. The Predict panel opens.
3. Use the lock icon to freeze the selection. Confirm Row Selected is Grand Total and Select Measure is Forecast.
4. Confirm the following default selections:

5. Configure the forecast as follows:

6. Under Choose Algorithm, confirm Trend Decomposition with MSTL is selected. Under Customize Algorithm, select both Year and Quarter under Set Seasonality. This tells the model to detect and account for patterns at both cycle levels at the same time. Select Run Forecast.

7. The forecast preview appears—historical data in grey, predicted values in green, with the confidence range shown as green shading.

8. Scroll down to see the forecast table. Select Save Forecast.

9. Confirm the forecast measure and the date range, and then select Save. The statistical forecast is created with values calculated at the most granular level of the row and column hierarchies, then aggregated up to higher levels. 

Close the period and extend the forecast horizon #

In this section, you reveal January 2026 actuals, close the period to lock them in, extend the horizon to January 2027, and reforecast the newly opened month.

Reveal January 2026 actuals

In this part, you add 2026 to the year filter to reveal January's actual Gross Revenue alongside the statistical forecast. Comparing the two values shows how closely the forecast matched what actually happened.

1. In the Filter panel, edit the existing year filter and add 2026 alongside 2024 and 2025.
2. Expand the column headers and compare the two January 2026 values side by side:

• Forecast: $2.09m—the value generated by the statistical model
• Gross Revenue: $1.92m—the actual January figure

Close January and extend the horizon

In this section, you close January 2026 to lock in the actuals and extend the forecast horizon by one month. This keeps a continuous 12-month forward view as each period closes.

1. In the Model ribbon, select the Period dropdown and select Close Period.
2. In the Close Period dialog box, select the Close period till dropdown and select Custom. Set the period to Jan 2026.
3. Select Extend Forecast Range and set Duration to 1 Month. This maintains a continuous 12-month forward window—as January closes, January 2027 is added to the horizon.

4. Select Preview. Verify the following, then click Save:

5. January 2026 is now populated with actual gross revenue and greyed out. The forecast horizon extends automatically to January 2027.

Reforecast January 2027

In this part, you reforecast the newly opened January 2027 period using January 2026 actuals as a baseline. This gives the extended month a realistic starting value instead of an empty cell.

1. In the Model ribbon, select Reforecast and select Reforecast Column. Configure it as follows and select Apply:

2. January 2027 is seeded from January 2026 actuals ($1.92M), giving a realistic baseline for the extended horizon.
3. Double-click the Jan 2027 grand total cell, enter +4%, and select the check mark. January 2027 is now populated at $2M.

Commit the forecast to the database #

In this section, you add a writeback destination, configure writeback settings, run writeback, and verify the results in the database.

For an overview of writeback concepts, see Writeback | Plan.

Add a writeback destination

In this section, you add the Fabric SQL database as a writeback destination for the forecast measure. This destination determines where committed values are written when you run writeback.

1. In the Writeback ribbon, select Add destination.
2. In Create Destination, configure as follows and select Add:

3. A confirmation message appears when the destination is added successfully.

Configure writeback settings #

In this section, you configure how the forecast is written to the database - the writeback type, which measures are included, and the destination. These settings determine what gets written and how each writeback run behaves.

1. In the Writeback ribbon, select Settings. The settings panel has four tabs—General, Data, Destinations, and Advanced.
2. In the General tab, select the information icon next to each writeback type to review the options:

3. Select Long with Changes. A warning appears confirming the destination will be turned off. Select Proceed.
4. Confirm Filter type is set to None—this writes back all data without filtering.

5. Select the Data tab. Deselect Gross Revenue—this is a read-only actuals measure and doesn't need to be committed to the database. Only Forecast should be selected.

6. Open the Destinations tab and select the Fabric SQL destination created in Add a writeback destination.

Run writeback and verify

In this section, you run writeback and confirm writeback saves forecast data correctly in the Fabric SQL database. You also review the writeback logs to see the run's status and details.

1. In the Writeback ribbon, select Writeback. A confirmation message that states, Writeback Completed, appears within a few seconds.
2. Open your training workspace folder and open the Northwind_FMCG SQL database.
3. Expand Northwind_FMCG > dbo > Tables and select the Forecast table. Verify the forecast data appears in the preview.

4. Navigate back to the plan app. In the Writeback ribbon, select Logs. Review the log details—status, duration, user who performed the writeback, measures written back, and writeback type.