13. Forecasting Churn Rates

Subtitles Enabled

Sign up for a free trial to access more free content.

Free trial


Having extensively analyzed past trends in churn, MRR and other variables of interest, this lesson will focus on forecasting trends into the future. We’ll see the benefits and limitations of Power BI’s forecasting capabilities.


Forecasting in Power BI

  • When we create a line chart representing a time series, we can add a forecast line that predicts the time series in the future
  • We can adjust the length of the forecast to suit the data
  • Seasonality should be set according to the data; here monthly data indicates a seasonality of 12

Ignoring the Last Points

  • Ignoring the last points starts the forecast before the end of the existing data
  • This lets us compare forecast values with actual ones to evaluate the usefulness of the forecast
  • However, it also reduces the number of data points used in making the forecast

Churn Rate and Sign-up forecasts

  • The forecast for churn rates seems plausible; notably, it displays the same seasonal trend evident in the past data
  • Forecasts further in the future are more uncertain than forecasts in the near future
  • The amount of data we have in this example makes future forecasts unreliable
  • We have longer data on signups, however, the forecasts for this field are also uncertain
  • This is because there is more variation and instability in the past signup data
  • Predictions will be more precise when the past data is more stable



Over the previous lessons, we've gained a lot of insight on the past and present performance of Innerslice.

In this final lesson, we'll create forecasts that predict future sign-up and churn rates. Power BI allows us to work as a time series Grable by adding a forecast line to a line chart. To do this, we'll create a line chart of churn rates and forecast churn rates for the next year. We'll start by adding a line chart to the left half of the canvas. And leave room at the top for a slicer.

We'll then navigate to the transactions table.

Add the transaction date field to the axis well.

And the annual MRR change rate field to the value, as well.

As before, we'll select the branch arrows twice to expand the chart down to the monthly level.

Next, we'll add a slicer to the canvas and place it above the line chart.

We'll add the MRR category field to the slicer and select churn.

We now have a line chart showing us the annualized churn rates for 2016 and 2017.

We would like to forecast churn rates for 2018. To do this, we'll select the line chart. Go to the analytics section of the visualizations pane.

Select forecast.

And add.

We can see an initial forecast is added to the line chart.

We'll adjust some of the forecast settings to improve this prediction.

The forecast length is one year of monthly data. So we'll change 10 to 12 and points to months.

We'll leave ignore last and confidence interval as they are for now. Seasonality refers to the length of the data cycles.

As our data is monthly, the seasonality value should be 12.

We'll press apply to accept these settings. And see that our prediction looks a bit better.

We now have a churn prediction for each month.

Notice that the prediction follows a pattern of the existing data. For example, the data for 2016 and 2017 shows a lower churn rate in July, August and September.

Our prediction for 2018 also shows a fallen churn for those months.

The gray-shaded area indicates the confidence interval.

We can be 95 percent confident that the actual churn value in a specific month will fall within this area. Notice that this area grows wider over time. This is because forecasting gets more uncertain the farther you go into the future.

Let's check the accuracy of our forecast by adjusting the parameters of the analytics line. We'll return to the forecast settings.

Change the ignore last setting from zero to six. And select apply. The model now ignores the last six months of the data set. As a result, the 12 month forecast now starts with the second half of 2017. This allows us to compare the forecasted churn rate for the last six months of 2017 with the actual churn rate.

It seems that the forecast is quite close to the actual values. Especially for the first few months of the prediction. As such, we can be reasonably confident in the accuracy of the forecast produced by Power BI.

Notice that the last two months of our 2017 prediction seem to be less accurate than the previous months.

This may be because we do not have enough data to forecast more than a few months into the future. In this situation, where we're ignoring the last six months, we're using 18 months of past data to predict 12 months of future data. This is probably not enough past data to reliably predict so far into the future.

Generally, we would expect forecasts to be more accurate when they follow a longer amount of past data.

To test this, we'll predict future sign-up rates. We have data on sign-ups from 2013 to 2017. So we may be able to make longer predictions than we can for churn rates.

We'll add another line chart to the right half of the canvas.

Navigate to the customers table.

Add customer ID to the values well.

Sign-up date to the axis well.

And expand down to the monthly level. We'll also clear the selection for the MRR category slicer so that we see all the customers who signed up over the last five years.

We'll then select the new line chart.

Go to the analytics section of the visualizations pane. Select forecast.

And then add.

As before, we'll change the forecast length to 12 months.

Ignore the last six months. Set the seasonality to 12.

And select apply to create the forecast.

We can see this forecast does not seem to be as accurate as the previous one. This is because the actual sign-up figures are prone to significant variations from month to month.

The forecast appears to be smoother than the actual data. So these variations are not picked up.

This also explains why the confidence interval is quite wide.

Although neither of these forecasts are perfect, they do provide Innerslice with a good indication of how churn rates and sign-ups will develop in 2018 should conditions remain similar to previous years. This concludes our Power BI case study course.

Through these lessons, we've seen many of the visualizations capabilities of Power BI and how they can be used to derive insights in a real life business situation.

With this knowledge, you can now apply these techniques to your own business and deliver similarly useful insights.

Power BI Case Study


My Notes

You can take notes as you view lessons.

Sign in or start a free trial to avail of this feature.

Free Trial

Download our training resources while you learn.

Sign in or start a free trial to avail of this feature.

Free Trial