How Forecasting in Power BI Works ( + Free Exercise File)

Forecasting is a tool businesses use to determine possible trends in the future. At the same time, it’s always a great way to cope with the future’s uncertainty.

Also, forecasting relies heavily on past and current data points. Without them, there will be no basis for the possible trends to happen.

Power BI is an excellent business intelligence and analytics solution. With it comes a basic forecasting tool that’s super easy to use.

And we’ll give you a walkthrough on how to do it using the Power BI Desktop.

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Again, for the forecasting tool to work, Power BI will need your past and current data.

For this example, we’ll be using the free exercise file we included in this article.

First, let’s import the file to Power BI.

To start importing the workbook, click the down arrow on the ‘Get Data’ icon found on the ribbon and select ‘Excel’:

Find the file and open it.

Then, select only the table which contains the data points.

Check ‘Forecasting Sample Data’ and click ‘Load’:

Now that the workbook is imported, let’s go to the next phase — creating a table and converting it to a line chart.

Let’s hit this off by creating a table out of the data we have.

Click the table icon on the visualizations pane and check both the ‘Date’ and ‘Sales’ fields on the fields pane:

To do that, focus your attention on the visualizations pane under the ‘Fields’ section.

Click the down arrow beside the field name and change ‘Date Hierarchy’ to ‘Date’:

Then, simply convert the table to a line chart since the forecast feature is only available on the line chart visual.

Simply click on its icon on the visualizations pane:

To add a forecast, go to the ‘Analytics’ section on the visualizations pane. Make sure that the line chart is currently selected.

Scroll down until you see the ‘Forecast’ part. Expand it and click ‘Add’.

Congratulations! You have just created a forecast on the data from the exercise file.

For example, here’s how the current forecast looks like:

As of now, it’s just a straight, diagonal line.

The forecasting settings available in Power BI are the following:

• Forecast length (by points, seconds, minutes, hours, days, etc.)
• Ignore last
• Confidence interval
• Seasonality

This time, let’s mix the settings up:

• Forecast length: 12 months
• Seasonality: 12 Points

The seasonality represents the complete cycle of peaks and dips in your data. Power BI automatically detects this but as you can see, it’s still a bit clunky.

The date we’re using has 2 years’ worth of data recorded monthly. The season is 1 year so that’s 12 points.

Press ‘Apply’ once you’re ready and you’ll see the effects immediately.

Cool! 👍

Some things to keep in mind when forecasting in Power BI:

• Only available for line charts
• The values in the x-axis need to be in date/time format or a uniformly increasing whole number
• The x-axis values also can’t be in text or decimal numbers
• For forecasting to work, the chart only has to have 1 line
• Should have fewer than 1,000 values in the line chart
• At least 1-day interval between the values

If you’re running a business and you’re using Power BI, learning how forecasting works is valuable. Doing it on your own could save you hundreds of dollars or land you a promotion.