A common question I receive from Tableau clients is the following: “I’d like the date range filter (start & end date) to automatically adjust, so the dashboard shows the most recent data upon opening it.”

Example of a date range filter:
Date range filter

If you are familiar with the filter's behaviour, this is certainly a valid question. When you open a dashboard with this type of filter, it will always revert to the settings when you published the dashboard. In this case: 01/11/2021 till 30/11/2021.

I'll explain how you can solve such a client's request.



Two remedies

Unfortunately, time will never stop, and once November 2021 has passed, there are two remedies.
  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!
 
Note: For this demonstration, I’m using the Superstore dataset.

Let’s investigate the “Value when workbook opens” function

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.

LOD expressions to the rescue

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])) }

The final steps to complete the setup

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.
 
Voilà!

Summary

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.
 
This makes this the most flexible date range selection method out there! 

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.
 

Psssst... never miss out!

We'll keep you updated* with our latest knowledge and share our newest events (e.g. training, webinars).
 
*You receive max. two newsletters a month, and you can unsubscribe at any time.