Scenario forecasting in Power BI

1. Scenario forecasting in Power BI

Welcome back! In this screencast, we will learn how to create a straight-line forecast for units sold. We’ll use two methods: first, by using last month’s growth rate, and then with a what-if parameter. Currently, the data model only has historical data between 2015 and 2021. Because a forecast looks to the future, we will need to create a new Calendar table that will extend the current timeline. We will also use this calendar to calculate growth rates between months. Start by creating a new table. We'll call this “Calendar”, and we'll use the CALENDAR function, MIN order date as the start date, and MAX order date plus 30 days as the end date so the calendar will go the full month into the future. Once this loads, we will need to connect this new table to the Sales_Fact table in the model view. Notice that the original dates field doesn’t work anymore. This is because of the data modeling. Don't worry! Simply switch out the old dates field for the new one. Great, now that the calendar is in order, we can create a growth rate. We are using a lot of steps here to break the calculation out into easy pieces, so follow along as each calculation builds on the next. So first, we'll create a new measure called “Total Units Sold”. We'll use SUMX, reference the Sales_Fact table, and then Units Sold. Now we need to find Total Units Sold Last Month. So we'll create a new measure, and using the CALCULATE function, we can filter Total Units Sold for last month using DATEADD. We want to make sure to use the Dates from the new Calendar table for consistency. To find the Units Growth, we'll create a new measure that simply subtracts Total Units Sold by Total Units Sold Last Month. Finally, we will create a measure to find the Units Growth %. We'll use DIVIDE to divide Units Growth by Total Units Sold Last Month, and we'll format this as a percent. That is the growth rate. But to do a straight-line forecast, we need to use last month's growth rate. So we'll use CALCULATE again on the Units Sold Growth % and filter for last month using DATEADD. Now to forecast this, all we need to do is multiply Total Units Sold Last Month by one plus units growth % last month. We'll add this to a line chart, and there we have it! We can also use a what-if parameter to forecast with the straight-line method. We'll go to modeling, then click new parameter in the what-if section. This box will pop up and allow us to create a range of inputs for the forecast. We’ll start by naming this Units Growth Scenario. We'll make these decimal numbers since the growth rate is a percentage. We want the minimum to be negative one and the maximum to be two, and we'll increment these by point 25. And we'll add a slicer to the page. Now, look what it did. It made a new table using GENERATESERIES. If we want to go to the Data view, we can see the range of values it created. It also created a measure using SELECTEDVALUE. When we use the slicer, this measure will know which value we selected, hence the name, and adjust the forecast accordingly. So now we'll create a new forecast using the Total Units Sold Last Month and one plus the parameter value. I’ll add this to the line chart. Now the cool part is we can adjust the forecast with the slicer, so this allows the scenario analysis to be a bit more flexible and is a valuable feature to have on a dashboard. Awesome! Now you’ve seen how to do it; it’s your turn to try it out.

2. Let's practice!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.