Forecasting Multiple Items with Seasonality in Power BI

Let us explore how you can quickly forecast multiple items with seasonality in Power BI.

Consider a scenario where your firm has accomplished the following in monthly sales in FY 2022 (totaling 1.16B in sales).

Your company is targeting to achieve 1.2B in sales for the upcoming year 2023. They want you to put together a quick forecast with the following considerations:

1. The product categories must have targets in proportion to their prior year performance
2. The plan should ensure seasonality of each product category & subcategory as observed in 2022

Once these two steps are complete, you will have a baseline plan on which you can make further adjustments.

In a scenario like this, here are three considerations:

• Forecasting with seasonality
• Forecasting for multiple items at once
• Forecasting at multiple levels at once - reflecting the hierarchical nature of the data. In other words, you forecast for Juices subcategory, Beverages category, and at the company level all at once.

Let us explore how we can accomplish this.

***

First, we shall take a look at the options available in Power BI for forecasting with seasonality.

Option 1 – Time Series Forecasting using line chart visual

One common method to forecast is to use the analytics feature in line charts.

However, this approach has the following shortcomings:

• You need a date field on the x-axis, which we do not have.
• You can only forecast one series at a time. It does not support simultaneous forecasts for multiple products. Even if you enable the legend option or small multiples in the line chart, the forecast option in the analytics section automatically disappears.

Option 2 – Forecasting by calculating seasonality weightage for past time periods

This is a more practical and flexible approach that we are likely to use in Excel. This is a two-step process:

• Calculate relative monthly weightages for the past periods
• Use the weights to distribute projections for future time periods

For example, the seasonality weightages for each product category has to be calculated as follows.

You can then use these weights to distribute the total forecast for each product category across months.

A slightly different approach is to create the weights based relative to overall company sales (instead of overall product category sales). Using this approach, you can directly distribute 1.2B across all categories and time periods.

The weightage-based method has the following drawbacks in Power BI:

1. The time dimension needs to be in rows, which is not the case with our data. In fact most Excel models have time periods across columns. This means that almost each user needs to spend time transforming the data, which slows down their productivity.
2. When you use DAX, the approach works better for a flat table (vs. a time or category hierarchy). Having hierarchies increases the model complexity.
3. This approach is tolerable for a single product (or a handful of products). In fact most examples for seasonal weightage method that you can spot online forecast outcomes only for one product or one company at a time. But this method does not scale. If you have 100 products, imagine having to calculate seasonality weights for each product across months. You will need to create 100 DAX measures.
4. This model is very difficult to maintain and manage. Many companies introduce and retire products regularly. Having products in columns is a sure way to render the report obsolete in no time.

Option 3 – No-code, seasonal forecasting using Inforiver in a few seconds

Inforiver allows you to overcome all of the setbacks listed above and helps you create seasonal forecasts in a matter of seconds.

This report shows 2022 Sales using Inforiver for Power BI. The data is scaled to millions. Let us now create 2023 Forecast with seasonality.

The first step is to insert a visual-level measure to hold 2023 Forecast values using the Data Input feature. The end state is shown in the image below. Note that you are only creating a single visual-level measure 2023 Fcst, which automatically replicates within each Quarter-Month combination. To ensure readability we have collapsed the Q3 and Q4 columns.

To create a forecast, select the blank cell ‘All – 2023 Fcst’. Double click and enter 1.2b (you can enter numbers in scaled format).

Press Enter. The value is not only written to the cell but also allocated uniformly across sub-categories (240M per product category for 2023) and time periods (20M each per product per month). Inforiver automatically highlights the updated values.

But we do not need this equal allocation. We need to allocate the forecasts proportionally based on 2022 sales. Click on the context menu appearing on the cell with the value 1,200.0 and choose ‘Distribute by weights of > 2022 Sales’.

Once this option is selected, the 2023 Forecast values are updated automatically across product categories & months by their relative contribution to 2022 Sales.

And before you realize, you have created forecasts with seasonality. And it takes almost no time, and it needs no data transformation.

You can quickly visualize seasonality by using the charting capabilities of Inforiver (this takes almost no time too). You will notice that the trend for 2023 Forecast mirrors the trend for 2022 Sales at all levels.

The best part of this approach is that you can operate at the speed of thought (well, almost). And it scales up even for 1000 products.

Other considerations:

You can accomplish a lot more using Inforiver for forecasting use cases. Some of them are listed below. We will cover each of them under separate blogs.

• Finer forecast adjustments: Once you have created the baseline forecast with seasonality, you can continue to make finer adjustments to the data. Inforiver supports both bottom-up consolidation and top-down allocations. In other words, changes to any cell is not only automatically rolled up to the top but also distributed proportionally to its descendants (row descendants & column descendants).
• Driver-based allocations: You can not only distribute values based on past performance but also based on other measures in your model. E.g., your support costs can be distributed across business units based on sales or headcount by business unit.
• Commenting & annotations: Inforiver offers a robust commenting and annotations engine that allows users to comment at a cell level. Users can also have conversations with each other at this level.
• Variance reporting: Once you have created forecasts, you can run variance reports comparing your forecast against a benchmark like prior period performance in a few clicks. No coding is required.
• Save data or write back to database: Finally, you can write back your seasonal forecasts to a database or shared drive. Inforiver supports destinations such as Azure SQL, SQL Server, Snowflake, BigQuery, SAP HANA, PostgreSQL, MySQL, OneDrive, SharePoint and more.

Try or buy Inforiver directly from Microsoft AppSource today.