Forecast data to predict future trends

Forecasting capabilities in the Planning sheet enable organizations to move beyond static annual plans and adopt agile planning methods, such as rolling forecasts and periodic re-forecasting. Use forecasting to project revenue, expenses, and other metrics for upcoming periods based on historical data.

Create dynamic forecasts directly on semantic models and update them as new actuals become available. Forecasts can be generated using multiple approaches, such as copying historical values, applying averages, or manually adjusting projections.

Prerequisite

The column dimension is a standard date hierarchy (for example, year > quarter > month).

Define initial forecast settings

The first step in configuring a forecast is to set the time frame for which the forecast is generated. Then configure how to populate static values in the forecast measure for past or closed periods. For instance, if you have actuals for 2025 and are generating a forecast for 2026, a static forecast measure will be created for 2025.

The forecast measure for previous periods cannot be edited. Static values for closed forecasts can be sourced from a measure in your planning sheet by setting Closed Period to Measure. Set Closed Period to Formula to define a formula to populate closed periods.

1. Go to Model > Forecast to create a forecast.

2. Set the start and end date of the forecast period.

3. Choose how to fill closed forecasts. Set Closed Period to Formula and enter the formula. When creating a formula, you can reference other measures in the planning sheet.

Static closed forecasts are populated using the specified formula.

Options to populate open forecasts

Generate open forecasts using one of these methods:

• Measure- If measure values exist for an open period, use those values to populate the forecast.

• Formula- If measure values exist for an open period, use a formula based on those values to populate the forecast.

• Data Input- Allow users to enter forecasts.

When open forecast measures are configured as Data Input, you can initialize the forecast using historical or current data. If the source data contains blank values, configure a default value using one of the options shown.

When a measure or formula is selected as the default value, ensure that data is available for the forecast period.

Populate open forecasts from the data source

If forecast values are already available for future periods in the data source, you can populate open periods using the native forecast measure or a formula that references it.

To initialize the open forecast from a measure in the planning sheet, set Open Period to Measure, then select the measure from Linked Measure.

To set forecast values based on a formula, set Open Period to Formula and enter the formula.

Consider a business case where the revenue projections are available for the forecast period, in this case, 2026.

To create a forecast based on a formula, set Open Period to Formula and enter the formula.

Create and save the forecast.

Initialize a forecast using historical or current data

Prepopulate future forecast periods using existing historical or current data. These initial values can then be manually adjusted by selecting and editing the cell.

1. Set Open Period to Data Input and Default Value to None.

If the measure used to initialize the forecast contains null values, you can replace them with a default value. The default value can be a static value, another measure, or a formula.

1. Select Create.

1. In Period Setup, From Copy Source, select the measure to use to prepopulate the forecast.

2. Apply Operation is set to Period Range, and Source Periods are automatically populated. The Revenue measure from January–December 2025 is used to initialize the forecast for January–December 2026.

The period range duration should match the target period duration. E.g., if the target period is 6 months, then you must select a period range spanning 6 months.

The forecast is initialized using revenue from the corresponding month in the previous year.

• To initialize forecasts with the average measure value over a specified period range, set Apply Operation to Average of Period Range and select the timeframe from Source Periods.

The average revenue from Q4 2025 is used to initialize each month in the 2026 forecast.

• To initialize forecasts with measure values from a single period, set Apply Operation to Single Period and select the period.

The revenue from December 2025 is used to initialize each month in the 2026 forecast.

Initialize forecast ranges using different methods

Split a forecast period into multiple ranges and initialize each range using a different method, such as average values or data from a prior period.

This option is available only when Open Period is set to Data Input.

1. Configure the initial forecast settings, then go to Period Setup.

2. Set the Target Period to January-March 2026, Copy Source to the Revenue measure, Apply Operation to Single Period, and Source Periods to December 2025.

1. Select Add Range.

2. Set the Target Period to April-June 2026, Copy Source to the Revenue measure, Apply Operation to Average of Period Range, and Source Periods to January-December 2025.

1. Select Add Range.

2. Set the Target Period to July-August 2026, Copy Source to the Revenue measure, Apply Operation to Period Range, and Source Periods to November-December 2025.

The 2026 forecast is split into multiple ranges based on the period setup configurations:

• The forecast for January-March 2026 is initialized from the December 2025 revenue.

• The forecast for April-June(Q2) 2026 is created from the average revenue from 2025.

• The forecast for July-August 2026 is based on the revenue from November-December 2025.

• The forecast values for September through December 2026 are blank because no initial value is configured in the Period Setup.

Update open forecasts

After a forecast is created, its initial values can be modified at any time. If the open forecast has been split into multiple periods, you can update the values for a specific period without affecting the configured values for the other periods.

1. Select Reforecast > Reforecast Column.

2. Define the period range used to update the open forecast.

3. Select the measure and method to populate the forecast.

The new configuration is applied to the July forecast.