If you have ever heard a commercial for an investment product, you will hear the disclaimer “Past performance is not indicative of future response”. That reminds me of a movie quote — You keep using that word. I do not think it means what you think it means. I disagree with the investment advisers. The past is a guide to the future as things tend to repeat in cycles, which is why when economic numbers are reported they are seasonally adjusted. For example Amazon hires people every year to help with the increases in sales in December. Fortunately there is a tool for that, in Power BI
Divining the Future
One manner which statisticians derive repeating seasonal patterns, or as statisticians prefer to call it periodicity, is via Exponential Moving Averages. I won’t bore you with the details, but this method is rather good at determining the future based upon the past, which is why Microsoft picked it as the method it is using in Power BI. There are of course a couple of caveats to keep in mind if you want to use Forecasting in Power BI. The first caveat is the visualizations only work on line charts. The second restriction is this feature works in the online version of Power View only, which means you can’t do this without having a Power BI license. While Predictive Analytics are often computed using Excel, this book will tell you how, the Power View feature I am referring to is called Forecasting, which is validated by the Hindcasting feature and needs HTML 5 rendering, which is only available online. Yes you can also use the App Microsoft has provided to view HTML 5, but you have to get that from the App Store which is still, on the internet, not within Excel.
Getting Forecasting to Work
The trickiest thing about getting the ability to see the Forecasting option is you need to have the date field, which more than likely will be on the X-Axis, formatted as a date the tool wants to use. This means you have to actually have a valid date. 201412, 201411, 201410… will not work as they are not dates. You will need instead to format these values as 12/1/2014, 11/1/2014, 10/1/2014 … or the forecasting feature will not be available. YYMMDD won’t work either. Unless your dates are formatted in this way the forecasting feature will not appear. Since this is a Power View feature, you may have to write some DAX code to format the data in this manner, or modify the data source.
To determine whether you thing the statistical model is valid, Hindcasting allows you to apply the same model to the past. Since you know what happened previously applying the same model to project the future can be overlaid on the data which really did occur in the past to determine how close the predictions applied to the future apply to the past. It has been said that Hindsight is the only accurate prediction, and overlaying the model will allow you to see the theory compared to the actual.
Putting it all Together
Using the statistical models available to predict the future can be a great tool, provided that you have the requisite tools and data formats necessary to accomplish this task. Having reviewed the details needed to get started, in a later post I will provide the DIY steps need to get started with Forecasting in Power BI.
Data aficionado et SQL Raconteur