Creating a Date Filter That Shows a User Defined Number of Previous Days/Months/Years

Answer

The following instructions can be reviewed in the workbook attached below. For this example, a three month period of time prior to the date selected by the sliding filter will be displayed.

Step 1: Create the Date Control parameter

This is the slider control that will be utilized to select the end date of the three month block. Effectively, if today's date is selected, the previous three months of data will be shown.

Right-click in the Parameters pane and select Create Parameter.
Ensure the following settings are in place:
- Under Data Type select Date.
- Under Current Value select the desired date.
- From Display Format select Automatic.
- For Allowable Values select Range.
- Under Range of Values select Set From Field.
- Select the date field. In this example, Order Date.
Click OK.

Step 2: Create a Date Range Control parameter

This is where the time frame of displayed data is configured. For example, a value of -5 will display data from the five months prior to the date set in the Date Control parameter.

Right-click in the Parameters pane and select Create Parameter.
Ensure the following settings are in place:
- Under Data Type select Integer.
- Under Current Value enter the desired value. For this example, a value of -3 is used for the prior three months.
- From Display Format select Automatic.
- For Allowable Values select All.

Click OK.

Step 3: Create the calculated field that will act as a filter

This calculated field will allow for the date to be selected in Date Control parameter control and display data based on the Date Range Control parameter.

Navigate to Analysis > Create calculated field....

Enter a formula similar to the following:

IF [Order Date] <= [Date Control]
AND [Order Date] >= (DATEADD('month', [Date Range Control], [Date Control])) THEN "Keep"
ELSE "Remove"
END

Where [Order Date] is the date field that is used by the underlying data.
Where [Date Control] is the slider control parameter created in Step 1.
Where [Date Range Control] is the range of dates parameter created in Step 2.

Note: The calculated field and parameter can be modified to show years, quarters etc as long as the underlying data supports that function.