Forecasting with seasonality in Power BI

Today I’m starting a small series about forecasting in Power BI. In this blogpost I’ll start with a basic example about forecasting with seasonality that I will build upon in the following posts.

Please note, that the monthly variation I am using in this example is taken from an existing table and not derived by statistical methods like the Excel FORECAST.ETS.SEASONALITY function. Here, we simply apply an existing distribution to expected future sales, but I will cover forecasts using those statistical methods in upcoming blogposts.

Todays starting point is a request to calculate how many sales to expect until the end of the year and then distribute the expected sales according to a list of monthly %, which are all different, because there is an expected seasonality in the sales:

Forecasting with seasonality

Forecasting with seasonality logic

The total amount of expected sales for the year should be based on the sales of the past:

1. Take the amount of the sales for the months, which are already closed
2. Lookup the sum of % for the closed months and add them up
3. Apply the rule of three to determine the expected sales of the whole year
4. Allocate the difference to the remaining months, based on their monthly %

The following picture gives an overview of the situation:

Overview

So we are in May and have some sales in already, but the last closed month is April (these months add up to 1,435 sales in total). Therefore the % from the Forecast table will be taken from January until April as well (they add up to 28%). Applying the rule of three forecast the amount of 5,125 for the whole year. We will distribute the share for the months May-Dec according to the % from the forecast table.

One additional requirement is that the solution should be dynamic: As soon as a new month is closed, the amount shall be automatically updated. So the base sum that determines the level includes one more month and the months with forecast values start one month later.

Data model

My tables are connected as follows:

Data model for forecasting

Solution with a DAX measure

This DAX-measure returns the desired allocation:

• The first variable “_previousMonth” determines the last closed month. For simplicity-reason, we assume that this will always be the last month before today. In a later blogpost I will show a solution where this can be individually selected. Please note, that the MAX-column that is picked from the Date-table needs to have the same granularity than the Forecast-table. Therefore, I’ve picked the column over which these 2 tables are connected.
• The next variable “_latestYTD” fetches the sum of sales that have been occurred up until the previous month. I’m referencing a YTD-measure here, which follows a fairly standard definition:
  CALCULATE ( [SumSales], DATESYTD ( ‘Date'[Date] ) ).
• Variable “_cumulPercentage” adds up the %-values of the closed months. Again, referenced YTD-measure has same logic than for sales above.
• Now we have all the values for the rule of three to calculate the total amount of sales of the year (“_amountForTotalYear” in row 20).
• Then comes the final calculation where we apply the %-values on the annual amount (for the open months) and simply take the actual sales amounts for the closed months. As these amounts shall be aggregated correctly on non-month intervals like year or quarter, we have to iterate over the months.
  • Therefore we take the whole Forecast-table (row 24), as we need the monthly granularity and also need to pick the %-value.
  • VAR “_isPlanningMonth” determines if the currently iterated month is closed or not.
  • VAR “_relevantYTD” then picks the fitting value for each month: Either the actual or forecasted (_amountTotalYear multiplied by the monthly %).
• All the months’ values will then be added up, because we are using the SUMX-function here (row 23).Wa

Outlook

Doing forecasts with DAX has become a very viable solution for me since the availability of a standard connector for DAX queries against Power BI datasets recently. This allows me to export my DAX calculations back into ERP- or bookkeeping systems in an automated way. Check out my next blogpost where I describe how this could be done for the example above.

In upcoming posts I will also cover how to:

• integrate more attributes into the allocation like products or cost centres
• create the %-table automatically based on previous years figures
• give an option to increase or decrease the forecast amount by certain %
• use statistical methods to create a forecast with seasonality based on historical data in Power BI

The file is available for download here: Forecast-1.pbix

Do you see other use cases that I should cover? Please let me know in the comments below.

Enjoy and stay queryious 😉