Creating Dynamic Data Actions and Tables

Planning workflow for automatic time determination:

1. Set the current year in the input control.
2. The input form (data table) automatically adjusts.
3. Run the data action.
4. The data action reads the current year selection via the input control.

In the preceding example, you select the planning year using an input control. The input form as well as the data action use the same input control to determine the current year, thereby resulting in fewer selections for you.

The input form is configured as follows:

• PY Actual is a restricted measure based on a fixed assignment for Version = Actual and a dynamic selection for year of: Previous Year.
• CY Fcst is a restricted measure based on a fixed assignment for Version = Forecast and a dynamic selection for year of: Year to Date.
• Hidden column is needed to determine the current year (hard coded) in the scenario.
• Year 2023 column header is a dynamic text widget.

In the previous example, 2023 is determined using dynamic text from an input control.

To create a restricted measure, use the Add Calculation option for Measure.

In the following example, the objective is to automatically determine the prior year so that the user doesn't have to select it.

When the advanced formula is run, actual data for 2022 is copied into the public forecast version for 2023. 2022 is determined by the formula (PREVIOUS(12).

In the preceding example, note the following rows in the Advanced Formulas Step:

• Row 1: The target year is determined via the TargetYear prompt.
• Row 2: The amount measure is copied.
• Row 3 Data for the prior-year actual is copied to the current-year forecast.