Forecasting in Power BI using Python

Published in

TheCyPhy

5 min readNov 9, 2022

Cities like these feel like the lighted-up motherboard of a computer. Are we in a simulation ?😱.

Hello Curious Person🙂 , If you want to create a dynamic forecasting dashboard in Power BI using the power of Python, then you have come to the right place. In my previous post I have done the same thing in Tableau so let’s play with Power BI this time.

Let’s get our data first, download it from here. Now it's time to jump to Power BI. Open Power BI, Now Click File → Get data → Text/CSV and select the AirPassenger dataset file. A dialog box will open up, Click on Transform Data.

It's time to enter the Power Query editor world. We should see something like this below.

First thing is to delete the “Changed Type” step from the “Applied Steps” section. We can do this by right-clicking the “Changed Type” step and then clicking on Delete.

Let’s create a parameter first, named “Forecast Period”, to do that Click on Home → Manage Parameters → New Parameter. Create a parameter as below

This Parameter will help us to set the number of time periods we want to forecast ahead. For example in our case now, the Current value 12 means we are going to forecast for 12 months ahead.

Now it's time to bring our old friend Python 🐍. First click on AirPassengers dataset then click on Transform → Run Python script.

It will open up a dialog box where we have to write our python script. Copy paste the below script there

# 'dataset' holds the input data for this script
import pandas as pd
from statsmodels.tsa.holtwinters import ExponentialSmoothingforecast_period = 12
dataset["Month"]= pd.to_datetime(dataset["Month"], format="%Y-%m")
dataset.set_index("Month", inplace=True)
model = ExponentialSmoothing(dataset, trend= "additive", seasonal= "additive", seasonal_periods=12,
                            ).fit()
actual = pd.Series(list(dataset["#Passengers"].values))
forecast = pd.Series(list(model.predict(start= len(dataset), end=len(dataset)+ forecast_period-1).values))
forecast = forecast.apply(lambda x: 0 if x < 0 else x)dataset.reset_index(inplace=True)
dates= pd.date_range(start=dataset.loc[len(dataset)-1,'Month'].date(), periods=forecast_period+1 ,freq= pd.infer_freq(dataset["Month"]))[1:]
d = {'Month': dates, 'Forecast':forecast.values}
dataset= dataset.append(pd.DataFrame(data=d))

After executing the above script, we will see a dataset with two columns “Name” and “Value”. Click on expand on the “Value” column and untick the “Use original column name as prefix” option. Then delete the “Name” column. Then Right click on the “Month” column and then click on “Change Type” and then change it to Date. Similarly, change the type of “#Passengers” and “Forecast” columns to Decimal numbers.

Now we should see something like below.

We can see, we have added a Forecast column to our dataset which contains forecasted values.

Now it’s time to make it a little bit dynamic. Click on “Run Python script” in the “Applied Steps” section and then expand the query editor.

We can see our Python code here, we have to modify it a little bit to make use of our “Forecast Period” parameter. Find the line “forecast_period = 12” and change it to forecast_period = “&Text.From(#”Forecast Period”)&”. Now our python code is connected to the “Forecast Period” parameter and whatever value we will give to the “Forecast Period” parameter, our python code will forecast ahead for that period of time. For example, in our case, if we set our “Forecast Period” parameter to 6, we will get forecasted values for the upcoming 6 months.

Note- If you faced some issues due to privacy levels in Power BI. We can resolve that by setting the privacy levels for Python and our Dataset. To do that Click Home → Data source settings → Global permissions. Select your dataset file and click on Edit Permissions. Then set the “Privacy level” to “Public”. Similarly, do it for Python.

Phewww 🤯, That’s a lot of work in Power query for now. Click on Home → Close & Apply. Now it’s time to do some visualizations using python. Click on the Python visual in the Visualizations section. Drag and drop the “#Passengers”, “Forecast” and “Month” columns in the “Values” field. Remove the Date Hierarchy from the “Month” column. In the python script editor, copy and paste the below code

import matplotlib.pyplot as pltax = plt.gca()dataset.sort_values(by=['Month'], inplace=True)  ## very important to sort the valuesdataset.plot(kind='line',x='Month',y='Forecast',color='red', ax=ax)dataset.plot(kind='line',x='Month',y='#Passengers', color='blue', ax=ax)plt.show()

And we are done with the work. We should see something like below

As of now, python visuals are not interactive as compared to other visuals in Power BI. So to do the above visual I will recommend Line Chart visual in Power BI.

Power BI has to catch up with Tableau in terms of integration with python. I guess Power BI needs some more Power 💪.

Congratulations on creating your dynamic forecasting dashboard in Power BI. Play with it and see the future 😎.

I will be back with more interesting articles like this, till then Enjoy your day 😇.

Feel free to connect with me at linkedin.com/in/ashish — kumar for a Cup of Chat

Forecasting in Power BI using Python

Written by Ashish Kumar