Advice how to structure rolling forecast formula with time element



I am looking to build on a formula in my rolling forecast. I am having trouble laying out the necessary steps.


Percentage Seasonality: Each period in a year (12) has an assigned rate. When I roll my forecast, I need the percentage to take the current forecast month rate / the sum of the remaining forecast periods' rates.


Any insight would be helpful. Thanks!



  • Misbah




    Step 1 : Create SYS Time Module where you have Current Month stored

    Step 2: In your Calculation Module - Have three line items created

                L1 = Percentage Rate[LOOKUP: SYS Time.Current Month] 

                L2 = TIMESUM(Percentage Rate,12) - Note: This Line item will not have Time as a dimension. Also it is assuming that you need                        12 months rolling all the time

               L3 = L1/L2


    Hope that helps!