Creating a new summary table with predictions

[Anonymous]

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.

[Anonymous]

@lbendlin 

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?

[lbendlin]

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?

[Anonymous]

@lbendlin 

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?

[lbendlin]

"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?

[Anonymous]

This Factor table has been computed by the client based on historical values.

Sector	Factor
Sector A	-15.55%
Sector B	75.53%
Sector C	4.85%
Sector D	63.30%
Sector E	15.38%

They feel that these factors are sufficient to project the values for the remainder of the FY. This means that, if the most recent data for this FY is till OCT21, then this is what they want:

NOV21 = NOV20 * Factor

DEC21 = DEC20 * Factor

(and so on till)

MAR22 = MAR21 * Factor

Unfortunately, this is what the client wants. I am trying to see if I can create a Summary table with existing data and these "projected" data.

[lbendlin]

That makes a lot more sense. Please provide some more sample data (at least back to Nov 20 ) 

[Anonymous]

Data for 2020-21:

Sector	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar
Sector A	427	366	320	349	412	472	571	320	349	412	349	412
Sector B	13	17	22	29	1	42	44	22	29	1	29	1
Sector C	0.08	0.15	0.11	0.13	0.17	0.12	0.14	0.11	0.13	0.17	0.13	0.17
Sector D	92	85	82	85	95			82	85	95	85	95
Sector E	104	67	34	61	26			34	61	26	61	26

Data for 2021-22 (so far):

Sector	Apr	May	Jun	Jul	Aug	Sep	Oct
Sector A	365	314	271	294	347	398	475
Sector B	61	65	63	51	54	0	0
Sector C	0.06	0.04	0.02	0.08	0.17	0.17	0
Sector D	100	97	87	89	87
Sector E	206	165	47	25	34

These are sample data.

[Anonymous]

Hi @lbendlin , does this sample data (above) help?