When working with time series data it is important to consider different time windows. From annual to seasonal data, charts using dates will often use drill-downs and filters to show these changes. Using parameters, we can create a time window that can be dynamically moved by the user. For this example, we are using the EU Sample Superstore dataset.
We begin by pulling our time series onto the columns pane of the sheet. By right-clicking and dragging, we can select Months as a continuous field to break up our chart by months. We now wish to create a parameter using this field, but we cannot directly create a parameter from this field. To get around this, double-click the field and copy-paste the resulting formula into a new calculated field, which I named "Month (Order Date)".
We are now able to create a parameter using our newly calculated field.
The default parameter settings are mostly appropriate in this case, with the exception of the display format. As this parameter will be seen by the user, we switch to a more easily legible format.
Having created our parameter, we can right-click and Show it on our worksheet. This will create a drop-down box that can be interacted with to change the parameter value. Pulling in the Sales measure to the rows of our chart, we can now use our parameter.
Under the Analytics tab, apply a reference line to the table.
The reference line can then be set to equal our Month Parameter. To create the background for our date window, we Fill Above with our desired colour.
Having created the start of the date window, we now create the end. Add a new calculated field (titled "End Date" in this example), we input the Month Parameter into the following formula, which will add 3 months on to the month selected in our parameter.
Applying our new End Date field to our chart as a detail, we can now add a second reference line to the chart, this time using End Date as the value and creating the end of the date window. To overwrite the previous "Fill Above" background from the start, we apply the colour of the chart background as a new Fill Above (in this case, the background is white).
Adding practical uses to the window:
We now have a 3-month date window that can be changed dynamically using a drop-down box. While currently only a visual effect, we can develop this further to create a calculation that will allow filtering using the date window.
With the following calculated field, we now have a Boolean output that defines whether a given data point is in or out of the window, allowing it to be used in future calculations.
Adding optional functionality:
In addition to a parameter changing the start date, we can also change the width of the date window dynamically. This can be done by creating an integer parameter and editing our End Date field to include it as follows:
Having included all of these steps, we are now able to create a dashboard that includes a dynamic date window where the position and width of the window can be interacted with by the user. This window can also filter other dashboard objects, such as the sales figure shown here: