Forecasting with PowerBI… really?

So PowerBI just added a Forecasting feature? As a Forecasting specialist, I got really interested in this new feature.

What all organizations are looking for is not only a tool to explore the past, but also to get insight about the future. No wonder it’s a great marketing tool for all BI platforms to come with a Forecasting tool that can provide insights from your data about predicting the future.

This feature will allow you to build sleek charts like this one, but how good is it really? Can we trust PowerBI for forecasting future trends?

8ec980b1-822c-4f72-bde7-13944329d088

How it works?

First let’s take a look at what we have. Follow this link to understand how to activate the PowerBI Forecasting feature. Now you can have PowerBI forecasting a Time Serie for you. Great but wouldn’t it be nice to understand HOW PowerBI forecasts it? What is the model used in background?

The PowerBI team answered this question in this post where they explained that the algorithm was the R ETS(A,A,A) or ETS(A,A,N), which is an Exponential Smoothing algorithm that you can choose to make Seasonal or not (seasonality will be calculated in an Additive not a Multiplicative way). I will refer you to this paper for more information about R and this ETS algorithm.

Back to basics: Forecasting 101

We will dedicate some more posts on Forecasting Time Series in this blog, so stay tuned!

What you should know in the meantime is that if you want to get a good forecast you pretty much need to go through all the following operations:

  1. Clean your data set from outliers:  you might want to remove exceptional values (think zero sales because you were out of stock, or an exceptional sale due to a promotion for example) from the baseline as theses values are not relevant and risk of getting your forecasting algorithm off-track
  2. Pick a forecasting algorithm and the values of its parameters: there are a bunch of methods you can use to forecast a Time Series: Time Series Decomposition, Moving Averages, Exponential Smoothing, Holt-Winters, ARIMA, Croston… to name a few. Each method comes with some parameters values that have to be defined and this is equally important. It’s much better to use a simple method with relevant parameters than a sophisticated algorithm with an absurd set of parameters.

How to best clean your data, pick the best algorithm and set of parameters for it are important and difficult questions that Forecasters have to answer. There is often no absolute answer and the algorithm that works best today will not necessarily be the best one tomorrow. So should you change every time you forecast or should you give more points to stability? Should you let the algorithm pick himself the parameters to use or should you fix them based on your knowledge of the business? Again important questions!

Actually these questions are so important that we touch here the core of what Forecasting really is: STRATEGIC! Forecasts drive Strategic decisions and Tactics.

As such Forecasts are usually reviewed at the highest possible level in Organizations. And the most important thing when presenting a forecast to a top executive is to be able to JUSTIFY how you built it: does it makes sense based on past trends and seasonality? The worst possible answer to justify your forecast is: “the machine did it”…

PowerBI Forecasting

The problem of PowerBI’s Forecasting module (as we are writing these lines), is that it answers all these strategic questions for you and leaves you with very little FLEXIBILITY to chose methods and parameters

  • data cleaning: PowerBI won’t really do it. At most it will perform “Linear extrapolation” between missing data points
  • you can pick if your algorithm should be Seasonal or not (thank you!), but the way to calculate seasonality will be Additive and nothing else (sorry but I actually usually prefer to consider Seasonality as Multiplicative…)
  • The algorithm will be the R ETS additive one, with linear trends and nothing else. If you want to use a different method or non-linear trends you can forget it
  • Parameters of the ETS are automatically picked (this is usually done by doing back testing and minimizing the error for the 1st time period forecasted). This can be quite dangerous because it’s not so hard to be accurate for the 1st time period, many methods and parameters can provide good forecasts for that. But what Executives and Top Management will likely look at is more the medium to long term forecast, and the parameter that minimizes the first month error is not necessarily the one that will provide the best medium to long term forecast.

Don’t get me wrong the R ETS algorithm is quite good and will provide excellent results in many cases. But I also know a lot of cases on which it is not appropriate, will give you absurd forecasts and you will be in difficulty when having to explain them to your Management. So for all these reasons it’s important to understand how this Forecasting PowerBI “Black Box” works, what it can do and can not do, and most importantly be careful and not trust it blindly like “PowerBI is so smart and tells me this so it must be true…”

Bottom Line: 

  • It’s great that PowerBI works on Forecasting features (we insist that these are still in beta version as we write and will be for sure improved in the near future). We are also happy to see that they use R which comes with awesome packages for Time Series analysis and forecasting, so this also goes in the right direction
  • However keep in mind that Forecasting is a highly strategic activity and being able to justify how you calculate your forecast is as important as the forecast itself. Therefore a little bit more control in the choice of the algorithms and parameters would be highly appreciated instead of having a “Black Box”. Why not also provide very simple “What if?” tools such as boxes for Forecasters to build a forecast based on simple strategic inputs from their management like Trend: +10%, Seasonality: 12 months… Come on PowerBI team, you know how much we love you on this blog so… challenge accepted?

AnalystMaster

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s