Forecasting in Power View and Power BI

Guest Post by Avichal Singh

Intro from Rob:  Never fear, last week’s series is still slated for completion, and in a special way.  Watch this space on Thursday for some fireworks.  For now, please enjoy Avi’s thoughts on the new forecasting component of Power View / Power BI.

PASS Business Analytics conference saw the announcement of a pretty cool Power View feature: Forecasting. I felt lucky to have been there and also to have had the opportunity to attend both of Rob Collie’s sessions (Data Revolution, Industrial Strength Excel). The Data Revolution session, I must say, was unlike anything I expected. No DAX formulas, no bullet points; just a path to data nirvana Smile

The Power View forecasting feature was cool enough that I just had to play with it! I wanted to try it out with a few real world data sets. I ended up using Climate Data and Stock Market performance.

- First a quick look at the Power View forecasting functionality
- Then I show you how I built the files using Power Query (The more I use that tool the more I like it)

You can find the link to the finished Excel file here. You can also watch me walk through the whole process in the video below:

Video Walkthrough: Forecasting in Power View and Power BI

Power View Forecasting in a Nutshell

In the ‘cloud first’ spirit that Microsoft has been following, the forecasting feature is only available in the online Power BI site (See microsoft.com/powerbi for more and to sign up for a free trial). To enable the forecasting feature, after opening your file on the Power BI site, you need to switch to the HTML5 mode by clicking on the icon at bottom right.

clip_image001

Click on this icon to enable the HTML5 mode with forecasting functionality

Power View Forecasting in a Nutshell


clip_image004clip_image005

Arrows indicate the direction you can click and drag using your mouse

clip_image007

Drag to right to forecast in future

clip_image009

Drag to left to hindcast. You can compare the faint blue line (hindcast) with the dark blue line (actual) to test how well the forecasting model performed.

clip_image011

Adjust specific points by dragging up or down. Use this to account for outliers which should not be considered or for what-if scenarios

clip_image013

Analysis pane activates automatically and shows advanced controls. Including ability to adjust the auto detected Seasonality and show Confidence intervals – a tight band indicates model is fairly confident about it’s prediction, a wide band may indicate that you take the prediction with a pinch of salt.

For more details on how Forecasting works see announcement by Power BI team.

Using Power Query to build the file

Data Sources Used

· Northern Hemisphere Temperature Anomalies
Temperature Anomalies = deviations from the corresponding 1951-1980 means
See: Why we use temperature anomalies and not absolute temperatures?

· Disaster Affected: Gapminder by Hans Rosling
For Subcategory=Disasters, I downloaded affected annual numbers for: drought, epidemic, extreme temperature, flood, storm and tsunami.
Check out Hans Rosling’s epic TED talk: The best stats you’ve ever seen

· MSFT Historical Quote Download: from Yahoo Finance
Check out article on how to build these URLs.

No, the Stock Market dataset is not related with the previous two Smile; I just wanted to try out something different.

Temperature Anomalies

This is an ugly file! Without Power Query I would probably never even consider pulling it in to Power Pivot. But Power Query actually makes the whole process fun!

Temperature Anomalies data loaded using Power Query

clip_image017

Header rows need to be removed

clip_image019

Columns need to be split and Unpivoted (moved to rows)

clip_image021

Extraneous columns need to be removed

clip_image023

Extraneous rows need to be removed

Below are the steps I applied to this unruly file and pulled it into Power Pivot; watch the video for detailed walkthrough.

Applied Steps in Power Query

Quick Note on Load Settings in the Power Query Pane:-

Load settings in Power Query

· Load to worksheet: Loads the resulting data into an Excel sheet

· Load to Data Model: Loads data directly into Power Pivot. Preferred option if Power Pivot is the final destination. Try to avoid loading it into a worksheet and creating a Linked table in PowerPivot; since you do not get the full benefit of PowerPivot compression with that approach when saving the file.

· Leave both options unchecked: This would only save the query. Very useful to save intermediate queries and then later combine them to generate the desired dataset. This saves you from having to save the intermediate results.

Disaster Affected

See main steps below for loading this dataset; watch the video for detailed walkthrough.

Import first data set (Drought Affected)
Then Duplicate that to make copies

clip_image026

In the Power Query Editor go to
View tab > Advanced Editor

Power Query Advanced Editor

Here I made simple changes to the script to get different datasets
Learn more about Power Query Formula Language (also known as “M”)

Power Query Advanced Editor

Once I had all datasets saved as queries…

clip_image029

I Appended them together and loaded into the Power Pivot model

Power Query Applied Steps

Stock Market Historical Quotes

This was fairly straightforward; I followed same approach as above to compile the Queries and save them separately before I append it and load it into Power Pivot model.

Forecasting Results

Temperature anomaly is certainly trending up Sad smile. But look at the shaded area showing a Confidence Interval of 1σ which covers ~68% of possible values. It covers a wide swath, indicating that a lowering of temperature anomalies is perhaps a possibility. There is hope for humanity still!

Temperature Anomaly Forecast using Power View

Also there certainly seems some correlation between the increased temperature anomalies and people affected by natural disasters; both ticking up in the early and late 1980s.

Power View showing corelation between Temperature Anomalies and Natural Disaster Impact

Given all the amazing work Microsoft Power BI team has been doing by building features like Forecasting, it’s no surprise that the forecast for MSFT stock is looking good! Oh, I better put the disclaimer Smile: Information is provided “as is” and solely for informational purposes, not for trading purposes or advice.

MSFT Stock Forecast using Power View

The opinions and views expressed in this post are those of the author and do not necessarily state or reflect those of Microsoft

4 Responses to Forecasting in Power View and Power BI

  1. “Also there certainly seems some correlation between the increased temperature anomalies and people affected by natural disasters; both ticking up in the early and late 1980s.”

    Questionable, since there are many other factors that could affect that data, most obviously population.

    We could probably do a bit of forecasting ourselves, without the need for Power View. We can predict that more people having forecasting tools and large data sets available to them will lead to, 1) more knee-jerk prognostications, and 2) quicker, easier debunking of statistically questionable correlation claims.

    Ya gotta take the bad with the good ;-)

    • Avichal Singh says:

      You are right in calling me out on that one Colin :-) I agree, the tools are only as good as the hands that are wielding it.

    • David B says:

      I think maybe sites like these should recommend picking up a basic book on statistics. Usually you can find really simple ones called things like “Statistics for business” which are great without going too deep. Also sites like Khan Academy has some good videos teaching us the basics so we can build even better models without creating terrible prognostication-tools.

Leave a Comment or Question