These instructions explain how to create a chart to display a rolling forecast that will update dynamically when the Current Period is changed.
This example explains the process by creating a rolling forecast for Sales Forecast figures over a six-week period for a one-year time frame. This process can be adapted for different time frames and more complex use cases.
When you are setting up the time frame, keep in mind that charts support a maximum of 200 items or data points— your time frame should not exceed this.
- Create a module that includes:
- Time, the current financial year in Weekly format.
- A Line Item, Sales Forecast in Number format.
- A Line Item, 6-week Forecast with Boolean format.
- Pivot, if necessary, to ensure Time is across columns and the line items are on rows.
- Enter data into the line item, Sales Forecast.
- Set 6-week forecast to True for all instances.
Filter Time, by applying a Filter to 6-week forecast to display all instances.
- Create a Line Chart from the entire grid and publish to a dashboard:
- Return to the module and enter a formula into 6-week Forecast to take the beginning of the Current Period and add 42 days:
START() < CURRENTPERIODSTART() + 42 AND START() >= CURRENTPERIODSTART()
- Refresh the dashboard. The chart will refresh to show data values from the beginning of the Current Period plus 42 days (six weeks).
START() < CURRENTPERIODSTART() + 42 AND START() >= CURRENTPERIODSTART() – 21
will show a forecast for 21 days before the beginning of the Current Period and 42 days after.