Dynamic Previous Year Value based on Filter Selection

By - Updated March 31, 2016

In this article we will introduce how to use application variable together with filter variable to display Last Year Actual in a form, as shown below.

Last Year Actual

If you are not familiar with application variable or filter variable, please refer to Using Application Variables and Configuring Form Filters.

In this form, column headers on Row 6 are fully controlled by FiscalYear filter. As you probably already know how to set the headers on Column C to O, we will mainly introduce how to set up a Previous Year header, i.e. Cell B6, in this form. Its value is defined by taking the selection of the FiscalYear filter back by one year.

Step 1. Create an application variable called “Previous Year” and defined as below. Here, ${[Time].[FiscalYear]} refers to the filter variable value of FiscalYear. The definition is driven by the context that the variable will be used within.

Application Variable

Step 2. Define the LAYOUT as below. Use FiscalYear as a filter variable here.

Kepion BI Modeler

Step 3. Define COLUMN as below. Use Variable button in the ribbon to add application variables and filter variables to the column.

Kepion BI Modeler

Then your form will perform as shown below.