Category: power BI Forecasting

In many situations there is a business need to look at forecasts of, for example sales.

The following table shows example data of measures created to use for a Sales Forecasting measure

Creating some Key Measures

Using DAX

We have a Sales Table that contains Quantity and a Products table that contains Price

The following is a calculated column to get the total sales from Quantity and Current Price for each row

Add a filter to your report page for the year in order to see the above table

To create the data for the table above, create a table and drag Date (Date table joined to sales)

Next create a new measure

This becomes column 2 of the above table. At each date it looks at the Totals Sales at that point last year

Create your 2 years ago measure

Next is the 3 years ago measure

These become the next columns in the table above

Creating the Sales Forecast using variables

Or, instead of creating three different measures to use for forecasting we can use variables (The 3 measures are basically set inside the forecast measure instead of creating all 4 measures

We can improve on this. We want our forecast to show a 5% growth rate

If you look at the sales forecast in a bar chart by day it would show you how much money you need to earn every day over the year based on the previous years data

Showing the cumulative Sales Forecast

Forecasts can now be derived by anything in your model. Product name etc

Another forecasting example using Power BI Analytics

Now if you go into the analytics pane

Scroll all the way to the bottom you will see forecasting. The following are the forecasting defaults

• Add: Forecast 1
• Forecast Length: 10 Points (10 days into the future because we are using   day)
• Ignore Last: 0 points
• Confidence: interval 95%
• Seasonality: Auto Points

Change Forecast Length to 12 months (Into the future)

Seasonality: Change to 365 points because there are 365 days in the year and this now takes you to a full forecasting year

this is a great way of playing with the Forecasting to see the effects of any change in the forecasting defaults

Currently you can only forecast on a line chart. Hopefully there will be lots more Forecasting updates in the future.