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.
Let’s get started! 🔍
*This tutorial is for Power BI Online (also called "Power BI Service").
Table of Content
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.
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.
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.
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.
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.
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.
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:
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 click ‘Add’.
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.
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
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.
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.
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.
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! 😉