1. Forecasting in Power BI
In this post, you’ll see how a new forward-looking feature in Power BI can forecast the future of your data using built-in predictive analytics. In this day and age of an ever-changing business insights (BI) landscape; simply generating insights from current/past project data to track project cost isn’t always enough –— project managers and business users within your organisation need to forecast future project costs so that they can take corrective action today and gain a competitive advantage tomorrow.
Step by step, we’ll show you how easy it is to flex your precognitive powers using Power BI’s out-of-the-box forecasting feature on sample project data consisting of tasks, costs and timelines. By the end of the post, you will be able to forecast the future cost of your projects using your current data.
Forecasting in Power BI is generally available and free to use so anyone with a Power BI account can predict the future costs of their projects! There are a number of visual parameters within Power BI that require simple configuration. Let’s take a look at what they are:
Forecast Length: This describes the number of points or the duration of time that Power BI predicts data for. This can be either of Year(s), Month(s), Day(s) etc.
Ignore Last: This allows the user to ignore a number of points within the dataset. For example, if the dataset uses project costs for past 12 months and we know that the costs for the last 2 months are incorrect and shouldn’t be included to forecast, you can do that with Ignore Last.
Confidence Interval: This option allows us to set the confidence interval which determines the probability of a real value being close to the predicted value i.e. there is 95% chance of the real value being within the range of the predicted value.
Seasonality: Datasets exhibit seasonality when the time value in the dataset exhibits a pattern. Using this option, we can specify seasonality in the given dataset whether it is yearly, quarterly or monthly.
Power BI’s forecasting feature lets you see your forecasting in reports on the web, in line charts and pinned tiles.
Consider we have sample project data as seen in below screenshot.
After importing the dataset into Power BI Desktop via the query editor, right click the data source and duplicate it. This will split our sample dataset in half and use the first-half of the data to apply forecasting and tally the forecasting results with the second-half of the data.
This step will result in a duplicate query being created. We then need to split both of the datasets so that each query contains half of the total number of rows. To do that we will use Remove Rows option under Home tab. From our original query, we will remove both the bottom 25 rows and the top 25 rows from the duplicated query. As the total number of rows in the sample dataset is 50, splitting out bottom and top 25 rows will even out the number of rows in each query.
Similarly, for the duplicated query we will remove the top 25 rows.
Finally, Close and Apply.
The Fun Stuff: Predicting Your Future Project Costs
We are going to make use of the line chart visual in Power BI and track project costs over the period below using our dataset with duplicated query.
As you can see above, the chart only displays our project costs over the period and now let’s see how Power BI can predict the future costs of our projects! Let’s switch over to the analytics pane of the visual and underneath the Forecast option, apply forecasting as shown below.
Power BI has predicted what could be our possible project costs based on the parameters configured. Now let’s compare that result of Power BI predictions with the actual data from our original query.
From the above Power BI forecasting of the project cost for the period of January 2015 is very close to the actual trend of the project cost for that period.
Ankit Patira, BI Specialist & Reporting Revolutionary at Sensei
Ankit is Sensei’s Power BI guru and specialises in Microsoft BI technologies, cloud based BI and self-serve and data analytics. Hailing form a development background, the data and technology enthusiast has extensive experience across a variety of industries and markets and takes pleasure in revolutionaising your reporting with dynamic, easy to understand and most importantly – meaningful insights.