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

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Forecasting is important for business for a lot of reasons — improve the sense of direction, keep the business updated, improve budgeting, and others.

Fortunately, it’s quite easy to do forecasting in Power BI.

Compared to Microsoft Excel, you could create a forecast with Power BI in less than 5 seconds.

In this article, you’ll learn how to do forecasting in Power BI step by step.

Kasper Langmann, Co-founder of Spreadsheeto

Let’s get started! 🔍

*This tutorial is for Power BI Online (also called "Power BI Service"). If you have Power BI Desktop, then click here and go to the online version.

Introduction to Power BI Forecasting

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.

But keep in mind the forecasting doesn’t exactly tell you the future — only the possibilities the future brings.

Kasper Langmann, Co-founder of Spreadsheeto

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.

Reminder:

If you would like to follow closely with this tutorial, you have to download the free exercise file we included. This workbook contains the data points we’ll be using in this article.

Get your FREE exercise file

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!

Download the FREE Exercise File

Download exercise file

Importing the Exercise File

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.

The exercise file for the Power BI forecasting tutorial

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 selectExcel’:

Select Excel after clicking the down arrow on the ‘Get Data’ icon

Find the file and open it.

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

CheckForecasting Sample Data’ and clickLoad’:

Load the table which contains the forecasting data points

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

Table to 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:

As you can see, the table shows the date hierarchy of the ‘Data’ field. We can’t have that if we would like to create a forecast.
Kasper Langmann, Co-founder of Spreadsheeto

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

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

Remove the date hierarchy view

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:

Creating a Forecast

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 clickAdd’.

Add a forecast on a line chart

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

However, your work isn’t done yet. Although Power BI automatically sets the forecast settings for you, it’s still a bit clunky and would certainly need your input.

Kasper Langmann, Co-founder of Spreadsheeto

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.

If you don’t know the seasonality of your data, consider using the number of data points you have in 1 cycle of your data.

Kasper Langmann, Co-founder of Spreadsheeto

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

PressApply’ once you’re ready and you’ll see the effects immediately.

Forecasting in Power BI with settings tweaked up

Cool! 👍

Limitations

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

Conclusion

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.

Though the steps are simple, it may not be the same case for new users. If so, you would need practice. Download the exercise file now and follow the steps! 😉

Kasper Langmann, Co-founder of Spreadsheeto