In this article, we’ll explore Power BI what if parameters and how they can be used for forecasting. We will be diving into its built-in features and advanced customization options. From leveraging the Line Forecast feature for time-series data to creating dynamic and interactive forecasts using the What-If Parameter, we’ll guide you through each step of implementing forecasting in Power BI.

Whether you’re a beginner looking to explore the basics or an advanced user aiming to refine your reporting, this article provides a comprehensive understanding of how forecasting in Power BI can transform your data-driven decision-making process.

Let’s explore the dataset that will be utilized to demonstrate forecasting in Power BI. The dataset consists of two tables:

1. Date Dimension Table: This table includes three columns — Date ,MMYY and Year.

2. Sales Table: This table contains three columns — Date, Sales, and Type.

These tables are linked through the Date field, and the Date Dimension Table is designated as the official Date table within Power BI.

Line Forecast in Power BI

The Line Forecast feature in Power BI is a built-in tool that uses historical data to project future values. It is particularly effective for time-series data.

This forecasting option is only available for Line charts. Additionally, the X-axis must have a continuous date. If you use customized columns like MMYY, this option will be automatically disabled.

In Power BI Desktop, select the Line Chart visualization from the Visualizations pane. Drag your time-series column to the X-axis and the numerical column to the Y-axis. We added Date in X axis and Sales in Y Axis here:

Navigate to the Analytics Pane (found in the Visualizations section).Select the Forecast option and turn it on.

Adjust the Forecast Length to specify how far into the future you want to predict. Set the Confidence Interval to determine the range of uncertainty. Define Seasonality if your data has recurring patterns (e.g., monthly or yearly trends).

Click Apply to generate the forecast. The forecasted values will appear as a dashed line, with a shaded area representing the confidence interval.

The forecast feature in Power BI offers various parameters. Let’s delve into each one and explore its functionality to effectively apply them to your dataset based on your requirements :

 Forecast Length

Defines how far into the future you want to predict data points. Specify the number of data points or a specific time range (e.g., 3 months, 12 weeks).The forecast will extend the line beyond the actual data to cover this range.

Confidence Interval

Represents the range of possible values within which the actual data might fall, giving a visual sense of uncertainty in the forecast.Adjusted as a percentage (e.g., 80%, 95%). Higher confidence intervals produce wider shaded regions around the forecast line.

Seasonality

Accounts for recurring patterns in the data, like seasonal trends or periodic fluctuations.Automatically detect seasonality (recommended if unsure).Manually specify a value (e.g., 12 for monthly seasonality or 4 for quarterly trends).

Ignore Last

Excludes recent data points from the historical data used for forecasting. Useful if the latest data is incomplete or unreliable.Specify the number of data points to ignore (e.g., last 2 weeks, last 3 months).Apply this option if you suspect recent data anomalies due to events like data entry delays or system outages.

Show/Hide Confidence Interval

Toggles the visibility of the shaded confidence interval around the forecasted line.Enable or disable this feature depending on your audience’s needs.

Forecast using What If Parameter

We have explored the built-in forecasting functionality in Power BI. If you wish to customize the forecasted values, you can leverage time intelligence functions along with the What-If parameter. Let’s understand this approach using the same data model as outlined.

In Power BI Desktop, create a line chart and set MMYY on the X-axis to display Month and Year, while placing Sales on the Y-axis. As shown in the resulting chart, the data will be visible up to December 2024, aligning with the available dataset. However, for dates in 2025, the chart will display blank values since there is no data available for that period.

The variable _Forecast calculates the forecasted sales for 2025 by:

• Using DATEADD: This shifts the date back by one year (-1 YEAR) to fetch the sales from the previous year (2024).
• Applying a growth factor: The previous year’s sales ([Total Sales]) is multiplied by (1 +.10).This represents the growth rate .If the growth rate is 10%, the factor becomes 1 + 0.1 = 1.10.

Add the forecast measure to the axis, and observe how the data begins to populate for the future months of 2025, based on the calculation using a 10% growth rate.

At the moment, we are setting the growth rate (10%) directly in the measure, but this can be made adjustable so that users can modify the growth rate themselves to observe different forecast scenarios. To achieve this, we will utilize the What-If Parameter feature in Power BI. Let’s explore how to implement it in the following section.

Create a What If Parameter

Navigate to the Modeling tab and click on New Parameter. From the dropdown menu, choose the Numeric range option.

Assign a name to the parameter, choose the data type (Decimal, as per our requirement), and specify the minimum , maximum, increment, and default values.

After the parameter is created, a slicer will automatically appear on the page, along with a new table named after the parameter. You can adjust the slicer’s display according to your requirements; in this case, we are displaying the values vertically.

We create an intermediate measure to retrieve the selected value from the slicer using the following DAX formula:

GrowthValue = SELECTEDVALUE(‘Growth Rate'[Growth Rate])

To link these values to the measure, modify the measure by replacing the hardcoded value (0.10) with the parameter value (GrowthValue), as shown below:

Users can now select the growth value directly from the slicers, and the forecast values will dynamically update accordingly. This functionality empowers users to visualize forecasts based on their chosen growth rate, making the process highly interactive and adaptable.

Growth Rate = .05

Growth Rate = .40

You can define the measure calculation according to your specific requirements and configure the parameter as needed. While we have provided an example based on Year, it can be tailored to align with your business needs.

Conclusion

This article has guided you through various forecasting techniques available in Power BI, including the built-in Line Forecast and the dynamic What If Parameters for scenario-based predictions. By understanding and implementing these approaches, you can unlock the full potential of Power BI to project future trends, make data-driven decisions, and simulate outcomes based on customizable parameters like growth rates. The addition of the What-If Parameter introduces interactivity and adaptability, allowing users to explore multiple scenarios effortlessly, making forecasting not only more precise but also more insightful.

If you require any assistance with Power BI or have further questions, feel free to reach out to us at Vidi Corp!