Adding Trend Lines & Forecasts in Power BI

Power BI’s trend lines and forecasts feature is a useful tool for data analysis and visualization. Your data-driven decision-making process can be improved by using trend lines and forecasts in your Power BI graphics. This post will provide step-by-step directions and screenshots for adding trend lines and forecasts to Power BI. 

Step 1: Load Data

You must first put your data into Power BI to begin. Excel files, CSV files, databases, and more are just a few of the many data sources that Power BI supports. Assume for the purposes of this example that we have a dataset with past sales data.

• Open Power BI Desktop.
• Click on the “Home” tab and select “Get Data.“

Get data.

• Choose your data source and load the data into Power BI. 

Super Store

Step 2: Create a Line Chart

Once your data is loaded,

load dataset

you can start creating visuals to analyze it. Open an existing report in Power BI Desktop or start a new one. In this case, we’ll create a line chart to visualize the sales trend over time.

• In the “Fields” pane on the right, select the relevant date column (e.g., “Date”) and a numeric column (e.g., “Sales”).

fields

• Drag and drop the date column into the “Axis” area and the numeric column into the “Values” area of a new visual. That is on X-Axis we are using Order Date by Year and Month hierarchy. On Y-Axis we are plotting sum of sales.

adding fields to chart visuals

• You should now have a basic line chart displaying your sales data over time.

line chart

Step 3: Add a Trend Line

To better understand the underlying trend in your data, you can add a trend line to the line chart. A trend line visually represents the general direction in which your data is moving.

• Click on the line chart to select it.
• In the “Visualizations” pane on the right, expand the “Analytics” section.

analytics pane

• Click on the “Trend line” option to add it to your chart, or just toggled the trend line to ‘On’ state. 

trend line

You may visualize and examine data trends using different types of trend lines in Power BI. Here are some typical trend line types:

• Linear Trend Line: The link between two variables is shown as a straight line using a linear trend line.
• Exponential Trend Line: An exponential trend line is used when data points increase or decrease at an increasingly faster rate.
• Polynomial Trend Line: A polynomial trend line is used when the relationship between variables follows a polynomial equation.
• Logarithmic Trend Line: A logarithmic trend line is used when data points increase or decrease at a decreasing rate and then level off.
• Moving Average Trend Line: A moving average trend line calculates the average value of data over a specified number of periods.
• Power Trend Line: A power trend line is used when data follows a power-law relationship.
• Seasonal Trend Line: A seasonal trend line is used to identify repeating patterns or seasonal effects in time series data.

Step 4: Customize the Trend Line

You can further customize the trend line to meet your visualization needs. Power BI offers several formatting options for trend lines.

• Click on the trend line in your chart to select it.
• In the “Format” pane on the right, you can adjust various properties such as line color, style, and labels.

formatting trend line

Step 5: Add Forecasting

Forecasting allows you to predict future values based on historical data. Power BI provides a straightforward way to add forecasting to your line chart.

• Click on the line chart to select it.
• In the “Visualizations” pane, expand the “Analytics” section.

add forecast.

• Click on the “Forecast” option to add it to your chart.

forecasting trend line

Power BI will generate a forecast for your data and display it on the chart. We can also format the trend line and forecast by following ways-

Customizing Trend Lines and Forecasts:

Power BI allows for easy customization of forecasts and trend lines. The forecast horizon (the number of future periods to anticipate), the confidence intervals (the degree of confidence in forecasts), and the kind of trend line (linear, exponential, logarithmic, etc.) are all variables that can be changed.

Advanced Forecasting Models:

Although Power BI’s built-in forecasting features are helpful, you can use Data Analysis Expressions (DAX) to construct more complex forecasting models. DAX is a formula language that enables intricate modelling and calculations. For more precise forecasts, you can, for instance, utilize the Prophet, ARIMA (AutoRegressive Integrated Moving Average), or exponential smoothing models. Using DAX to implement these models gives you more control and customization over your forecasts.

Seasonal Decomposition:

When making forecasts, it’s critical to take seasonality into consideration when your dataset displays seasonal patterns. To comprehend and visualize seasonal patterns in your data, you can use seasonal decomposition techniques in Power BI. This entails decomposing your data into seasonal, trend, and residual components so that each one may be separately modelled and forecasted.

Dynamic Reference lines

X-Axis constant line:

To highlight a particular constant number on the X-axis, you can add an X-axis constant line to a trend line chart in Power BI. To draw attention to a threshold or reference point, employ this technique. The steps to add an X-axis constant line in Power BI are as follows:

• Create or Select Your Chart: Launch Power BI Desktop and get your Power BI report. To add the X-axis constant line to a chart visual, either create one from scratch or choose one that already exists.
• Select the Chart Visual: To choose a chart visual, click on it. On the canvas, the chart should be highlighted.
• Configure the X-Axis Constant Line: Expand the “Format” section in the “Visualizations” pane on the right-hand side. Expand the “X-Axis Constant Line” option by scrolling down to find it.

x axis constant line

• Toggle the X-Axis Constant Line Switch: Under the “X-Axis Constant Line” section, there is a switch marked “Constant line.” This switch should be turned “On.”
• Set the Constant Value: You can choose the constant value, line color, and line style when you enable the constant line. In the “Value” section, type the constant value you want to show on the X-axis. The value at which you want the constant line to appear on the X-axis should be this. Here we have added constant value of July month 2022. And selected the line type as dashed with orange color.

x-axis constant line

Y-Axis constant line:

Now based on the similar steps as discussed above, we can plot y-axis constant line also. Select the Y-Axis constant line field under the visualization pane. Here we are selecting line type as solid with blue color and constant y-axis value as 60k.

y-axis fields

Below Y-Axis constant line can be seen as follows-

y axis constant line

Min Line:

On the same Visualization pane, we can select for the Min Line which is used to plot the base reference line for Y-Axis field that is the minimum value for Y axis field (Sum of Sales).

min line

Max Line:

Similarly, on the same Visualization pane, we can select for the Max Line which is used to plot the base reference line for Y-Axis field that is the maximum value for Y axis field (Sum of Sales).

max line