Power BI Forecasting Model Using Python

by | Power BI

Forecasting is an important aspect of data analysis, as it allows businesses to make informed decisions about the future based on historical data. One efficient way to perform this task is by utilizing Power BI forecasting model using Python. Power BI is a popular business intelligence tool that allows users to create interactive data visualizations, reports, and dashboards. 

In this tutorial, we learn how to create a forecast model in Power BI using Python. We will use Python in Power Query to create forecasted values and bring those into the visualizations of Power BI. You can watch the full video of this tutorial at the bottom of this blog.

Power BI Forecasting Model Samples

Below are some Power BI forecasting models to show you what we want to achieve in this tutorial. These are actual page views revealing weekly seasonality and some seasonal peaks in the data. 

Power BI Forecasting Model Samples

Towards the end, we can see the increasing trend in our data which we want to pick up on in our model.

Power BI Forecasting Model Samples

Power BI Forecasting Model: Controls and Limitations 

Below is the model for the 30-day forecast created using Power BI. It has the same seasonality as the actual page views, and in Power BI, we also have the options to control some of the data.

Power BI Forecasting Model: Controls and Limitations

We can do that by opening Analytics in the Visualizations pane. Then, hover down to Forecast >  Options

Power BI Forecasting Model: Controls and Limitations

Put 30 days in the Forecast length and set the Confidence interval at 95%. The system can predict the Seasonality with its default setting, but we can also add 7 to represent weekly seasonality. 

Power BI Forecasting Model: Controls and Limitations

Click Apply and we should get a model similar to the one above.

Trend Analysis For Python And Power BI Forecasting Models

Power BI does a great job of modelling seasonality. However, its trendline does not perform the same.

To start the trend analysis, turn on the Trend line in the Visualizations pane.

Trend Analysis For Python And Power BI Forecasting Models

Once on, we can see an upward trend. We should be able to add that trend into our data which will then influence the forecast. 

Trend Analysis For Python And Power BI Forecasting Models

We can do that with our Python model. As we see in the model below, the trend picked up seasonality instead of staying flat.

Trend Analysis For Python And Power BI Forecasting Models

Using Python Code For Forecasting

Using Python to achieve our goal is not a difficult task. To start, open your Jupyter Notebook

Power BI Forecasting Model Using Python

Bring in the data that we need: pandas, matplotlib.pyplot, seaborn, and the ExponentialSmoothing

Power BI Forecasting Model Using Python

There are other models that will probably be more accurate, but they will require more optimization. 

We will also bring in seasonal_decompose to see the seasonality and trend. Then, use the web forecast web_forecast.xlsx to read our data. 

Power BI Forecasting Model Using Python

Then, switch the date using the code below. 

Power BI Forecasting Model Using Python

Set the index of our dataset to Date and call it ts. Then, set the frequency of the dataset. We know that we have daily data, so let’s set the frequency as d as in day and save it as ts

Power BI Forecasting Model Using Python

Finally, plot using ts.plot ( ).

Power BI Forecasting Model Using Python

After plotting, we should see exactly what we saw in our Power BI notebook.  

Power BI Forecasting Model Using Python

To get a better idea of the components in our actual trend, we can use the following code.

Power BI Forecasting Model Using Python

The first model is our Actuals. Next to it is the trend line which we pick up with seasonal_decompose(ts).plot();.

Power BI Forecasting Model Using Python
Power BI Forecasting Model Using Python

This is the trend that we need to add to the model. 

Power BI Forecasting Model Using Python

We also have the seasonality that we can add to both the Power BI and the Exponential Smoothing model. 

Power BI Forecasting Model Using Python

Our last model shows the Residuals or those that are unexpected in the data which are represented by points. Notice that as we move toward the end of our data, we can see that there are a lot more events happening. 

Power BI Forecasting Model Using Python

Training The Model

With our model, we need to train our data which is typically followed by testing. However, in this case, we won’t be testing our model because we’ll just use what the model gives us. 

There are 298 days in our dataset, but in this example, we only need the model to remember 290 of those days. This is because we don’t want to give the model all the data that it can’t learn and will eventually just copy. 

Basically, we have this training set of 290 days out of 298. 

Next, use ExponentialSmoothing for our model. Then, pass in the training dataset which is 290 days, and use add (additive) for our trend, mul (multiplicative) for our season, and 7 for the seasonal periods. Then, fit that data into the model.

Additive & Multiplicative Trends

Let’s have a quick overview of what the additive and multiplicative trends are.

In an additive model, the trend slowly adds, whereas in the multiplicative model, it exponentially increases and there is quite a lot going on as well. We can use either of the two to get a different type of prediction. 

We can play around with the additive and multiplicative methods to modify our prediction. Our present data are clearly growing so it’s imperative to use additive, but we can also try using multiplicative to see what we’ll get. 

For example, change the seasonality from mul to add

Run the data and observe how the prediction changes.

Similarly, we can change the trend from add to mul

This should yield a multiplicative trend that is a little bit bigger. 

After trying the possible combinations, using mul for both the trend and seasonal was found to yield the BEST result. 

Once we have this forecast model, we can then use it to forecast 30 days in advance. 

Power BI Implementation 

Let’s create the same Power BI forecasting model in our Power BI notebook.

In our Power BI Forecast, go to Visualizations > Analytics > Options. Notice how we set the Forecast length to 30 days. 

Let’s see how we can implement that code very easily in Power Query. 

Click Transform data. 

In the Power Query Editor, bring in the data and Add Custom column for the Category. Use Actuals so we can later split the Actuals from Forecasts. 

If we go to the Forecasts Query, we’ll see a smaller data set equivalent to 30 days into the future.

Reviewing The Python Script 

Our Python Script contains similar information. First, we bring in a dataset, save it as df, change Date to datetime, and set the frequency to d (day). 

We also bring in our ExponentialSmoothing model from holtwinters. We take the first 290 days as our training set and then add that data to the model. 

In our ExponentialSmoothing model, we add the training data and set both the trends and seasonal to mul (multiplicative) and the seasonal periods to 7 days. Then we fit our model.

Next, we get a new data frame or table with our forecast. We reset the index and make sure they are named Date and Pageviews to match what we have in our original data. Finally, we click OK. 

In the output, we are given all these variables within the data. 

Go to Applied Steps and click Added Column. This opens a table with our predicted values and the custom column which has Forecast as the category. 

In the next query, we simply append the two data sets where we have the Actuals and the Forecasts

Click Close & Apply. 

The model slightly changed when we applied the multiplicative method. 

Compared to Power BI, we can easily do a forecast and optimize the model a little bit more by changing the additive nature of the trend and the seasonality in Python. We can likewise add those predictions to our actual data set. 

***** Related Links *****
Creating Forward Forecasts in Power BI Using DAX
How To Do Trend Analysis In Power BI Using DAX
Managing Seasonality In Your Budget Analytics – Advanced Power BI

Conclusion

In this blog, we walked through the process of creating a forecast model in Power BI using Python. By integrating Python into Power BI, we can access a wide range of data analysis and modelling tools, which allows us to create more advanced forecasts. 

With the skills you have learned in this tutorial, you can now create your own forecast models in Power BI and use them to plan for the future with confidence. Remember that forecasting is an iterative process, so don’t hesitate to experiment with different algorithms and techniques to find the one that works best for your data and continuously check and update your model as new data comes in.

All the best,

Gaelim Holland

Related Posts

Using the DISTINCT Function Effectively in DAX

DAX Table Functions Deep Dive

Explore an in-depth analysis of DAX table functions in Power BI, comparing SUMMARIZE and ADDCOLUMNS, and understanding INTERSECT and EXCEPT for enhanced data manipulation and analysis.