Hi everyone,
I have a dataset with seasonality percentages for different distributors. However, not all distributors have seasonality data for every month, and the available months vary per distributor.
Problem:
- Distributor A has seasonality from January to September 2024.
- Distributor B has seasonality from January to May 2024.
- Other distributors have different availability patterns.
- From October 2024 onward, I need to forecast seasonality using the last October seasonality per distributor.
To solve this, I wanted to create a matrix where:
- Rows = Distributors
- Columns = Months (January – December, ignoring the year)
- Each cell = The most recent available seasonality value for that month, from the same month in a previous year
Example Expected Output:
| Distributor | Jan 24 | Feb 24 | Mar 24 | Apr 24 | May 24 | Jun 24 | Jul 24 | Aug 24 | Sep 24 | Oct 24 | Nov 24 | Dec 24 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | 2.1% | 2.3% | 2.4% | 2.5% | 2.6% | 2.7% | 2.8% | 2.9% | 3.0% | 3.1% (Oct 23) | 3.2% (Nov 23) | 3.3% (Dec 23) |
| B | 1.5% | 1.6% | 1.7% | 1.8% | 1.9% | 2.0% (Jun 23) | 2.1% (Jul 23) | 2.2% (Aug 23) | 2.3% (Sep 23) | 2.4% (Oct 23) | 2.5% (Nov 23) | 2.6% (Dec 23) |
For forecast months (October – December 2024 and beyond), the system should pull the last available seasonality percentage from the same month in the previous year (2023).
This matrix would serve as input for my forecast calculations, where each month should take the latest available seasonality percentage without considering the year.
Current issue:
- When extending the forecast to 2025, I expect January 2025 to use the latest available January value (from 2024 or earlier), February 2025 to use February's latest value, and so on. Pigment shows no data when doing the calculation because there is no seasonality for 2025 months.
- So far, I haven’t been able to ensure that 2025 months correctly pull the last available value per month
Question:
How can I ensure that:
- Missing months in 2024 pull the latest seasonality value from the same month in previous years?
- The forecast for 2025 correctly inherits the last available seasonality percentage for each month, ignoring the year?
Any help would be greatly appreciated! 