How to build Tableau's most flexible data range selector in the world

1. Each dashboard user changes the date range manually every time they open the dashboard.
2. The dashboard creator edits the dashboard and sets new defaults for the date range.

Both solutions are not ideal because they require manual work. Users will quickly get annoyed by changing the date range when they reference the dashboard. And the dashboard creators usually have more important work to do than changing date range filters each time period.

 

Luckily, we can use parameters to circumvent the rigidness of the standard date range filter. Since Tableau 2020.1, a new feature for parameters has made our life a bit easier. It’s called “Value when workbook opens”. Let’s see what we can do with it!

 

Let's first list all the (order) dates there are in this dataset:

 

 

Upon first trying to use the new function, I got confused. When creating a new parameter, I could only select "Current value" for said option, which didn't help.

 

 

Then my thought process was like this:
"I want the most recent date in my dataset to be in that parameter when the workbook opens, so if I take MAX([datefield]), that will work!"

Therefore I created a calculated field to do so. But still, I wasn't able to select the "Max Order Date" calculated field to be the current value of the parameter when the workbook opens.

The reason for this is that MAX(datefield) is an aggregation dependent on the filters used in the dashboard or sheet.

The value of it will change if other filters and parameters get changed. This leads to an unpredictable value, and thus we need to fix it so that it's independent of all the other filters.

To overcome this, the FIXED LOD expressions are a perfect fit! Let's change the Max Order Date calculation to be independent of other filters*.

*You might think: "Won't context filters mess up the LOD calculations?". In short: no they won't. The LOD calculations used for filling the parameter's current value are calculated before the worksheets are loaded. So even before context filters are applied. Thus it's safe to use context filters without worrying about impacting the parameters.

{ FIXED : MAX([datefield])} 

or even shorter: 

{ MAX([datefield]) }

Now, I can select the Max Order Date calculated field as input for the parameter's current value!

So, now every time someone opens this workbook or dashboard, this parameter fills with the most recent date available in the data source.

The next step to replicate the regular date range filter would be to create another parameter which we also prefill with a date upon opening the workbook or dashboard using the same method. This time, we need to create a negative offset from the previously created Max Order Date.

{ FIXED : MAX(DATEADD('month',-3,[Order Date])) }

This then leads to the following setup:

 

 

Now we still need to filter the data in the view!
Create a new calculation in which we use both parameters:

[Order Date] >= [Order Date Start]
AND [Order Date] <= [Order Date End]

Drag this to the filters shelf and only select True.

 

Using this solution allows the dashboard creator to set a fixed interval of time (in this example, 3 months) displayed when the dashboard opens. Plus, it will enable the user to change that interval in both lengths and start & end date.

 

The only thing you're giving up is the slider handles to change the dates, but that's a small sacrifice to make this date range dynamic based on the underlying data.