Forecast Data using Power BI

With the September Community Preview Power BI analytics tool allow us to do simple forecasting with data without doing complex DAX or any other mechanism. You can download it from September release

After successful Power BI desktop install you have to enable Forecasting feature from Preview Features.

Go to File - > Options and Settings - > Options

In the Options menu click the Preview Features tab and check Forecasting

Now the forecasting feature has been enabled successfully in your Power BI desktop version. Next step is to create dataset. For that I used AdventureWorksDW2014 database dbo.vTimeSeries view. I make some aggregations for existing data and create another view on top of that in order to make the demo successfully. Because for time series data we have some constrains like the time data variable must be Date data type likewise. And the existing view contains multiple records for particular month by reporting date. I obtain the Summation of the Sales amount for each month. Here is the code.

 
USE AdventureWorksDW2014
GO

CREATE VIEW [dbo].[vForeCast]
AS (
SELECT [CalendarYear]
 , [Month] 
 , SUM(Amount) AS Amount 
 , CAST(LTRIM(RTRIM(CalendarYear)) AS CHAR(4)) + '-' + IIF(LEN(MONTH) = 1, '0'+CAST(Month AS CHAR(1)) , CAST(Month AS CHAR(2))) + '-01' AS Date
FROM [dbo].[vTimeSeries]
GROUP BY [CalendarYear], [Month]  
);

Now, go to power BI desktop and Get data in order to import data in to the data model. (Please, note that I'm not give the steps for connecting to a 

Give the server credentials where I need to connect with

From the next window in Navigator pain select the data source.

Now data is successfully loaded to the Power BI data model.

Remember, we need to take care of our data types. The date field data type need to be Date data type. Lets go to the Reporting tab.

Select Line Chart and data you need to plot.

When you select Fields you have to select one Measure and date field. In my case I select Amount as measure and Date field.

In the Axis window down arrow pick Date instead of Date Hierarchy (Because we are dealing with dates in the time series)

Now the Line chart will looks like below.

Now go to Analysis tab in the right. At the bottom there is a forecast pane. Expand it and ready to configure.

You can configure Forecast lengths (How many data points or period (Months , Quarters), confidence interval etc. 

After do the configurations click apply. You can now see the forecast in the line chart as below.