skip to Main Content

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 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
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

Avi Singh

Avi Singh has personally experienced the transformation and empowerment that Power BI can bring - going from an Excel user to building large scale Power BI solutions. His mission now is to share the knowledge about Power Pivot and Power BI.

This Post Has 8 Comments
  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 😉

    1. 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.

    2. 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