How To Create A Forecast Model In Power BI With Python

Summary

This tutorial demonstrates how to create a forecast in Power BI using Python, enhancing the built-in forecasting feature to capture both seasonal patterns and trends in data. The video guides through using exponential smoothing in a Jupyter notebook, adjusting for seasonality and trends, and integrating the Python model's predictions back into Power BI. It highlights the advantage of optimizing models in Python over Power BI's default settings and encourages further learning through additional resources.

Takeaways

• 📊 The tutorial demonstrates how to create a forecast in Power BI using Python to enhance the built-in forecasting feature.
• 🔍 The script identifies seasonality and trend in the data, which are important for an accurate forecast model.
• 📈 Power BI's forecasting feature can visually forecast data with seasonality and trend, but Python can offer more customization.
• 🤖 The use of Python allows for the implementation of models like exponential smoothing, which can be adjusted for seasonality and trends.
• 📝 The script explains the process of decomposing a time series to understand the underlying trend and seasonality.
• 📚 There are different models available for forecasting, such as additive and multiplicative trends, which can be optimized for better predictions.
• 🔧 The tutorial walks through the process of training a model using a subset of the data to prevent overfitting.
• 📅 The forecast model can predict data for a specific period, such as 30 days in advance, which is demonstrated in the script.
• 🔄 The process of integrating the Python forecast model with Power BI is shown, including adjusting the data and appending forecasts.
• 🛠 The tutorial highlights the advantage of using Python for more detailed model optimization compared to Power BI's built-in tools.
• 📘 Additional resources like webinars and further learning materials are available for those interested in deepening their knowledge on forecasting.

Q & A

What is the main topic of the video script?

The main topic of the video script is how to create a forecast in Power BI using Python, specifically focusing on handling seasonality and trends in the data.

What type of data is being analyzed in the script?

The data being analyzed in the script is page views, which shows some peaks and a weekly seasonality pattern along with an increasing trend in the last month.

How does Power BI's forecasting feature handle seasonality?

Power BI's forecasting feature can visually forecast data and automatically picks up seasonality. Users can adjust the seasonality by adding a specific number, such as seven for weekly seasonality.

What is the significance of the 95% confidence intervals in the forecast?

The 95% confidence intervals in the forecast provide a range within which the actual values are likely to fall, indicating the reliability of the forecast.

Why is the trend line turned on in the visual?

The trend line is turned on to identify and incorporate the upward trend in the data into the forecast model, which is not being picked up well by the Power BI model alone.

What Python library is used for the exponential smoothing model in the script?

The script uses the 'statsmodels' library for the exponential smoothing model, specifically the Holt-Winters method.

What does the term 'additive' refer to in the context of the trend in the exponential smoothing model?

In the context of the trend, 'additive' refers to a model where the trend component is added to the data in a linear fashion, as opposed to a 'multiplicative' trend, which grows exponentially.

How does the script handle the training of the exponential smoothing model?

The script trains the model on a subset of the data, specifically the first 290 days out of 298, to prevent overfitting and allow the model to learn effectively.

What is the purpose of the 'seasonal_periods' parameter in the exponential smoothing model?

The 'seasonal_periods' parameter is used to define the length of the seasonal cycle in the data, which is set to seven days in the script to match the weekly seasonality.

How does the script implement the forecast in Power Query?

The script implements the forecast in Power Query by creating a new custom column for the forecast, appending the forecast data to the original dataset, and ensuring that the columns match for easy comparison.

What are the advantages of using Python for forecasting over Power BI's built-in feature?

Using Python for forecasting allows for more optimization of the model by adjusting the nature of the trend and seasonality, and it enables the inclusion of detailed predictions directly into the actual dataset.

Outlines

00:00

📊 Power BI Forecasting with Python

This paragraph introduces a tutorial on creating a forecast in Power BI using Python. The speaker demonstrates the Power BI's built-in forecasting feature, which visualizes data trends and seasonality. The data set in question shows weekly seasonality and an increasing trend over time. The tutorial aims to enhance the model to capture both the seasonality and the upward trend more effectively than the default settings in Power BI. The speaker also mentions the use of a Python model to achieve this, and introduces the tools and libraries used in the process, such as pandas, matplotlib, and the exponential smoothing model for a more customized forecast.

05:01

🔍 Analyzing Seasonality and Trend with Python

The second paragraph delves into the technical aspects of using Python for forecasting in Power BI. It discusses the use of exponential smoothing with additive and multiplicative trends and seasonality, and how these affect the forecast. The speaker explains the difference between additive and multiplicative trends in the context of the data's growth pattern. The paragraph also covers the process of training the model on historical data and using it to forecast future values. The speaker illustrates how to implement the Python script within Power Query to integrate the forecasted data with the actual data for a comprehensive view.

10:04

🚀 Optimizing Forecasts and Integrating with Power BI

The final paragraph wraps up the tutorial by discussing the advantages of using Python for forecasting over the default Power BI tools. It highlights the ability to optimize the model by adjusting the nature of the trend and seasonality, which is more limited in Power BI. The speaker also shows how to append the forecasted data to the actual dataset in Power BI, providing a seamless integration of historical and predicted values. The paragraph concludes with a call to action for viewers to like, subscribe, and visit the Enterprise DNA website for more resources, emphasizing the continuous support and educational content available for Power BI users.

Keywords

💡Forecasting

Forecasting in the context of the video refers to the process of predicting future values based on historical data. It is central to the video's theme as it demonstrates how to use Power BI and Python for making predictions about future data points, such as page views. The script mentions using Power BI's forecasting feature and later introduces a Python model to improve and customize the forecast.

💡Power BI

Power BI is a business analytics service by Microsoft that provides interactive visualizations and business intelligence capabilities. In the video, it is the primary tool used for data analysis and forecasting. The script discusses Power BI's built-in forecasting feature and how to utilize it to predict data trends.

💡Python

Python is a high-level programming language widely used for general-purpose programming. The video script describes using Python in conjunction with Power BI to create a more customized forecast model. It is highlighted as a tool for implementing complex models and optimizing forecasts beyond the capabilities of Power BI alone.

💡Seasonality

Seasonality refers to the predictable and recurring fluctuations in data that occur within a calendar year. In the script, seasonality is identified as a weekly pattern in the page views data. The video shows how to adjust for seasonality in both Power BI and the Python model to improve the accuracy of forecasts.

💡Trend

A trend in data analysis is a general direction in which something is changing over time. The script identifies an upward trend in the data, indicating that page views are increasing over the last month. The video explains how to incorporate this trend into the forecasting model to ensure that the forecast reflects the data's direction of change.

💡Exponential Smoothing

Exponential smoothing is a time series forecasting method for univariate data that can be extended to support data with a systematic trend or seasonal component. The script mentions using an exponential smoothing model in Python, specifically the Holt-Winters method, to capture both trend and seasonality in the data.

💡Confidence Intervals

Confidence intervals provide a range of values within which the true value of a parameter is likely to fall, with a certain level of confidence. In the video, a 95% confidence interval is used in the Power BI forecast to indicate the reliability of the predictions.

💡Jupyter Notebook

A Jupyter Notebook is an open-source web application that allows creation and sharing of documents that contain live code, equations, visualizations, and narrative text. The script describes using a Jupyter Notebook to implement the Python code for the custom forecasting model.

💡Holt-Winters Method

The Holt-Winters method, also known as the triple exponential smoothing method, is a time series forecasting method that includes parameters for the level, trend, and seasonality components. The script refers to this method when discussing the Python model, indicating its use for more accurate forecasting by accounting for trend and seasonality.

💡Additive vs. Multiplicative

Additive and multiplicative are terms used to describe the nature of the trend and seasonality in a time series. Additive models assume that changes are constant over time, while multiplicative models assume that changes are proportional. The script discusses experimenting with both additive and multiplicative trends and seasonality in the Python model to find the best fit for the data.

💡Optimization

Optimization in the context of the video refers to the process of adjusting the parameters of a forecasting model to achieve the best possible predictive accuracy. The script explains how changing the nature of the trend and seasonality from additive to multiplicative in the Python model can optimize the forecast results.

Highlights

Introduction to forecasting in Power BI using Python for more control over seasonality and trends.

Power BI's built-in forecasting feature effectively handles seasonality, especially weekly patterns.

Identifying the limitation of Power BI in capturing strong upward trends in the data.

Switching to Python in Jupyter Notebook to enhance the forecasting model by incorporating trends and seasonality.

Using exponential smoothing in Python as the forecasting model for better accuracy.

Introduction to Seasonal Decomposition to visualize and separate trend and seasonality in the data.

Explanation of how the exponential smoothing model works, including the concepts of additive and multiplicative trends.

Experimenting with additive and multiplicative models to optimize the forecast based on the data's behavior.

Training the model on 290 out of 298 days of data to allow it to learn and forecast accurately.

Visual comparison of the additive and multiplicative models' impact on the forecast.

Demonstrating how to implement the Python forecast code within Power BI’s Power Query editor.

Merging actual and forecasted data in Power BI to visualize the complete forecast model.

Highlighting the flexibility and advantages of using Python for more tailored forecasts compared to Power BI's built-in options.

Encouragement to explore different models and settings for better forecasting outcomes in specific scenarios.

Conclusion with resources for further learning, including videos and webinars on forecasting and seasonality decomposition.

Transcripts

[Music]

thank you

hi I want to show you how to create a

forecast in power bi using python of

course we can utilize the power bi

forecasting feature

which allows us to visually forecast

the data that we have to a specific day

and as you see here these are our

actuals they're just page views we can

see there are some peaks in the data but

it looks very seasonal and you can see

that this is a weekly seasonality but we

also see this trend where we see

increasing data in the last month and we

want to be able to pick that up in our

model

so if we go down here to the power bi

model you can see that we have a

seasonality and we can definitely

control that seasonality so I can click

into this and if I go over to the

analysis icon and I hover down to

the forecast feature you can see what I

did I forecasted 30 days out

and then we have a 95 confidence

intervals that I didn't have it was able

to predict the seasonality but you can

just add seven because we know it's a

weekly seasonality and if we apply it

nothing's going to change because power

bi does a very good job in picking up

that seasonality the only thing that

we're not being able to pick up very

well is the excellent Trend here so if I

turn the trend line on on this visual

you can see that someone of upward trend

going and we should be able to add that

Trend into our data which will influence

the forecast now we're able to do that

with our python

um

model and you can see that instead of

staying flat here we're able to pick up

this trend and seasonality and I'm going

to walk you through how to do that it's

very easy so I'm going to jump over to a

Jupiter notebook and then I'm going to

jump back here and just show you how

easy it is to implement the code

so what we're doing in our Jupiter

notebook we're just bring in the data

that we need we're bringing in pandas

matplotlibs Seaborn which we won't use

but the model we're using is exponential

smoothing now there are other models we

can use that will probably be much more

accurate but they will take a little bit

more optimization

then I'm bringing in seasonal decompose

because I want to show you how we can

see the seasonality and Trend and then

I'm reading our data in using the web

forecast here

what I'm doing here is just switching

the date I'm setting my index for my

particular data set to date and I'm

calling that data set TS

then I need to set the frequency of that

data set so we know that we have daily

data so I'm just setting the frequency

as d as in day and saving over it then

if I plot that you can see exactly what

we saw in our power bi notebook

now here's what we can use to get a

better idea of the components in our

actual Trend so this first line is your

actuals you can see it there

but you can see the trend line here

which we pick up with this seasonal

decompose and we did do a video

previously on how to decompose a Time

series so you can see here that this is

a trend that we need to add to the model

here's a seasonality that we can add to

both power bi model and the exponential

smoothing model and you can see that

here are the residuals or kind of the

things that are unexpected in the data

and those would be these particular

points here and you can see as we get

down to the end of our data we can see

that there's a lot more events going on

there

so with this model we actually need to

train it so the model is going to train

on some data and then if we can test the

model on some data we don't need to test

this because

um we're just going to use what the

model gives us

now there are 298 days in our data set

and I told the model to remember

290 of those days because we don't want

to give the data the model all of the

data because then it can't learn it's

just copying

so we have this training set which would

just be the first 298 days out of that

two for the 290 days out of the 298

and then what I'm going to do is I'm

going to use our model

so you can see I've saved a variable

model and then I am using exponential

smoothing I'm passing in that training

data set which is 290

um days now I'm for my Trend I'm using

an additive

and for my seasonality I'm using

multiplicative and for the season

seasonal periods I'm using seven and

then I'm fitting that data to the model

now what does that additive Trend in a

multiplicative trend mean so let me give

you an idea of that

so if we look at this visual here you

can see if it's an additive model it's

slowly adding

you can see it slowly adds and then for

a multiplicative model you can see

there's quite a lot that is going on is

exponentially growing so we can actually

add that

um seasonality additive or

multiplicative to get a different type

of prediction now I played around with

it and usually you might play around

with using a multiplicative here and an

additive here to get a better idea but I

think we can see for my data it is

really growing when it comes to

um the trend so I thought maybe

additive but we can use multiplicative

to see what we would get and then the

seasonality here we can see that it's

slightly growing also so like I said we

can definitely change these so if I

change both of these to additive and I

run that particular uh

data

you can see it changes the actual

prediction so if I add multiplicative to

our model so mul

and run that so now we have a

multiplicative trend

you can see that trend

is a little bit bigger so if we add

multiplicative to both so mul to Trend

and seasonality and this would be you

optimizing that model to give you the

best result

so

once we have this forecast model we can

use it to forecast 30 days in advance

and that's what we're doing here we're

using it to forecast a whole month in

advance and we are doing the same in our

power bi notebook

so

you can see if I go back to our power bi

forecast

look at the options we just doing that

30 day length and now I'm going to show

you how I implemented that code very

easily within the power query so I'm

going to go to transform data

okay

so now that we have that data in what we

in the power query editor what we have

we have our actuals and all I did was

bring in the data and then added a new

category uh with a custom category Uh

custom column and use actuals because I

want to be able to split between our

actuals and forecasts

so now if I go to forecast it you can

see it's a much smaller data set it's

that 30 days in the future

and if we look at our python script you

will see similar information here I

brought in a data set saved it as DF

because it's just easier to write I've

created a date change the date to

actually a date time set the index to

date set our frequency

to day then I brought in our exponential

smoothing model from Holtz winter

I took the first

290 days as my training set and then I

added that data to our model here so the

model is our exponential smoothing model

we add the training data we're using the

trends here and I think we said we liked

multiplicative so I'm going to change

that back to mul and then we fit we gave

it to seasonal periods which would be

seven days which we could see in our

data then all I did was get a new data

frame or table with a forecast I reset

the index and I just made sure they were

named date and page view to match what

we have in our original data so I'm just

going to hit OK with that

and you can see we are given all the

variables within that data

but we'll let the script run for a

second

then if I go down to

what we have we have our predicted

values here and then we have uh just a

custom column which I have forecast now

that we have all the same columns I just

appended the two data sets where we have

you know the actuals and the forecast

then I'm just going to close close and

apply this

hopefully we didn't do anything to

damage what we already created

and now you can see that model slightly

changed when we got to the

multiplicative side of things so we can

definitely easily do a forecast and

maybe the advantage over the power bi

one is that we can optimize the model a

little bit more uh by changing the the

additive nature of the trend and the

seasonality which we are only given the

option for seasonality here and also we

can add Dell's predictions into our

actual data set so I hope that helps you

get a little bit better idea of how we

can use forecasting uh there's a lot of

different models out there but I kind of

wanted to just give you a taste of what

we could do we do have a few videos uh

one of the videos I did before on

seasonality decomposition which would be

very helpful and there's a webinar that

we have that goes over forecasting

please ask any questions and I hope that

helps hey everyone Thanks for tuning in

to Enterprise DNA TV if you enjoyed the

contents covered in this particular

tutorial please throw the video a like

it really helps us and we really

appreciate it also don't forget to

subscribe to the Enterprise DNA TV

channel we we have a huge amount of

content coming out all the time from

myself and a range of content creators

all dedicated to improving the way that

you use power bi and the Power Platform

lastly check out Enterprise DNA's

website Plenty of resources and further

learning that you can access very easily

all the best

take care

[Music]

[Music]