Forum Discussion
Helper IIforecast based in last years proportion
Community Championninakarsa based on what you asked I have assumed some data for the current year actuals as following and added an index to the base table using M
The source table looks like this now
| Month | Last Years bookings | last year booking % of total | Assumed Current Year Actual | Index |
| Jan | 140 | 0.011141 | 180 | 1 |
| Feb | 160 | 0.012733 | 90 | 2 |
| Mar | 200 | 0.015916 | 300 | 3 |
| Apr | 450 | 0.035811 | 85 | 4 |
| May | 700 | 0.055706 | 65 | 5 |
| Jun | 900 | 0.071622 | 280 | 6 |
| Jul | 1500 | 0.11937 | 800 | 7 |
| Aug | 2500 | 0.19895 | 354 | 8 |
| Sep | 1000 | 0.07958 | 725 | 9 |
| Oct | 1500 | 0.11937 | 213 | 10 |
| Nov | 500 | 0.03979 | 89 | 11 |
| Dec | 3016 | 0.240013 | 56 | 12 |
Now this is a running total problem to me and I have solved it in this way
The measures are following
Assumed Current year total sumx:= SUMX('Table','Table'[Assumed Current Year Actual])
Assumed Current year running total:=
VAR _1 = MAX('Table'[Index])
VAR _2 = CALCULATE(CALCULATE([Assumed Current year total sumx],ALL('Table'[Index]),'Table'[Index]<=_1),ALL('Table'[Month]))
RETURN _2
last year booking % sumx:= SUMX('Table','Table'[last year booking % of total])
Last year booking % running total:=
VAR _1 = MAX('Table'[Index])
VAR _2 = CALCULATE(CALCULATE([last year booking % sumx],ALL('Table'[Index]),'Table'[Index]<=_1),ALL('Table'[Month]))
RETURN _2
Helper IIthanks for your response.
so what I am trying to do is calculate the forecast for 2020 based on the % monthly proportion of 2019.
For example, last year January consiting had a monthly proportion of total bookings of 0.011141175.
So going into this year if the actual bookings for Jan 2020 are 100 then if we say 100/0.011141175 this will give us the total forecast for 2020, and then each time the month closes to repeat the same proces. For example say February finished at 180, then to forecast end of year this would be (actual jan +actual feb)= 100+180 / (last year monthly proportion of total bookings)