Forum Discussion
Creating a new summary table with predictions
I have a client requirement for a stacked area chart that also needs to show forecasting. Based on my research and prior conversations in this Forum, it looks like Power BI has forecasting only for Line Graph and not for Stacked Area chart. Even in Line Graphs, if there are multiple series, forecasting isn't available.
Given this restriction, I'm trying to do a bit of Excel-based prediction and then using that to create a new summary table (with forecast) and generate a Stacked Area chart.
For starters, this is how last year's data looks:
| Sector | Apr | May | Jun | Jul | Aug | Sep | Oct |
| Sector A | 365 | 314 | 271 | 294 | 347 | 398 | 475 |
| Sector B | 60 | 65 | 60 | 42 | 0 | 0 | 0 |
| Sector C | 0.03 | 0.02 | 0.04 | 0.08 | 0.17 | 0 | 0 |
| Sector D | 100 | 97 | 87 | 89 | 87 | ||
| Sector E | 206 | 165 | 47 | 25 | 34 |
This is the predicted change for each of the sectors:
| Sector | Factor |
| Sector A | -15.55% |
| Sector B | 36.15% |
| Sector C | 4.85% |
| Sector D | 63.30% |
| Sector E | 15.38% |
I have values till current month for current year. How do I apply the prediction factors to the data to generate a new table in Power BI till the end of the FY? Once this table is generated, I can use it as the basis for my Stacked Area chart. But this seems to be a challenge. Any thoughts?
Thanks.
8 Replies
You may want to explain a bit more how you like to forecast. Based on last month only, based on all available data, based on a sliding window? Should the forecasted results be part of the next forecast?
We have data for FY 2020-21 at a monthly level. We also have data for FY 2021-22 till the current month (e.g. OCT). The client has used some logic (mostly, past years' data) to generate prediction factors for each of the sectors.
I plan on creating a new Summary table that looks like this:
Sector, ..., AUG21, SEP21, OCT21, NOV21, DEC21, ..., MAR22
The values for this table will be existing values until OCT21. But for columns NOV21 to MAR22, I will need to multiply the NOV20 value for that sector with the Prediction Factor. This will eventually create a summary table with current and predicted values. I can then show a Stacked Area chart of this table.
Does this help?
"I will need to multiply the NOV20 value for that sector with the Prediction Factor" - not following the logic here. That's not really how forecasting works. Please elaborate. Did you mean to implement a sliding window?
We have data for FY 2020-21 at a monthly level. We also have data for FY 2021-22 till the current month (e.g. OCT). The client has used some logic (mostly, past years' data) to generate prediction factors for each of the sectors.
I plan on creating a new Summary table that looks like this:
Sector, ..., AUG21, SEP21, OCT21, NOV21, DEC21, ..., MAR22
The values for this table will be existing values until OCT21. But for columns NOV21 to MAR22, I will need to multiply the NOV20 value for that sector with the Prediction Factor. This will eventually create a summary table with current and predicted values. I can then show a Stacked Area chart of this table.
Does this help?
