Display 'Last Year Actual' In Form

Author: Joyce Zhou

Introduction

In this article we'll show how to use an Application Variable together with a Filter Variable to display "Last Year Actual" in a Form, as shown below.

If you're not already familiar with Application Variables and Filter Variables, please refer to the linked articles before we begin. Now let's take a look at how we can configure the Form above.

Example

In the Form, notice that the column headers are controlled by a FiscalYear filter. As you should already know how to set these headers, we'll focus primarily on how to set up just the Previous Year header, i.e., the highlighted column above. 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 define it as:

${[Time].[FiscalYear]}.lag(1)

Here, ${[Time].[FiscalYear]} refers to the Filter Variable value of FiscalYear. The definition is driven by the context that the variable will be used with.

Step 2. Define the LAYOUT as shown below. Use FiscalYear as a Filter Variable here.

Step 3. Define COLUMN as shown below. Use the Variable button in the ribbon to add the Application Variables and Filter Variables to the column. 

Tip: Check out this article on how to configure rolling forecasts for more information about the Prior Months and Forward Months variables used here.

Once all that is set, your Form should behave as shown below. Notice that changing the FiscalYear filter moves everything forward a year, while the first column continues to lag behind.