We have the following data action to make a forecast for the next remaining months based on the index of the previous months. The index is calculated on past months. This index is used for the calculation of the future months. The below script will calculate the specific “PERIOD_FORECAST” (for example period 10 or 11 or 12) by multiplying the Budget for period 10 times the calculated index over the past 9 periods:
// Context
MEMBERSET [d/Measures] = “AMOUNT”
MEMBERSET [d/BEDRIJFSNR] = %COMPANY_FILTER%
MEMBERSET [d/Date] = %PERIOD_FORECAST%
MEMBERSET [d/NIV4] = %RUBRIEK%
// Empty data
DATA([d/Date] = %PERIOD_FORECAST%) = 0
// Formules
DATA([d/Date] = %PERIOD_FORECAST%) =
((RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE%, [d/Date] = “202301”) + RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE%, [d/Date] = “202302”)
+ RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE%, [d/Date] = “202303”) + RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE%, [d/Date] = “202304”)
+ RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE%, [d/Date] = “202305”) + RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE%, [d/Date] = “202306”)
+ RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE%, [d/Date] = “202307”) + RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE%, [d/Date] = “202308”)
+ RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE%, [d/Date] = “202309”))
/
(RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE_VERGELIJK%, [d/Date] = “202301”) + RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE_VERGELIJK%, [d/Date] = “202302”)
+ RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE_VERGELIJK%, [d/Date] = “202303”) + RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE_VERGELIJK%, [d/Date] = “202304”)
+ RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE_VERGELIJK%, [d/Date] = “202305”) + RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE_VERGELIJK%, [d/Date] = “202306”)
+ RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE_VERGELIJK%, [d/Date] = “202307”) + RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE_VERGELIJK%, [d/Date] = “202308”)
+ RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE_VERGELIJK%, [d/Date] = “202309”)))
* RESULTLOOKUP([d/Version] = %BASELINE_INDEX_VERSIE_VERGELIJK%, [d/Date] = %PERIOD_FORECAST%)
We would like to avoid the hard coded “2023xx” and replace it with a flexible solution