When building a dashboard, one of the most common elements is a date-filter. And the most used date filter is probably the infamous ‘Range of Dates’.

Problems of ‘Range of Dates’ and other filter-types

At first it seems like a nice filter, but the slider is not very user-friendly to set a date-range. And users soon start to complain about the fact its always showing the full date range of the dataset. If you change the range before publishing the workbook, both start and end-date will always stay fixed: it not dynamic.

The ‘relative’ date can (should) be useful, but it offers both too few and too many options – you can’t select a self-defined period, but users can select for example ‘next three months’, which is confusing in 99% of all dashboards…

Creating a better date filter

Therefor we create an alternative, in which we can choose between a selection of useful date periods, but also any custom date range. We are using (of course…) the default Superstore dataset.

Part 1: Custom date range using parameters

First we are going to replace the range-filter by a filter with a start- and end-date. This is very similar to the Tableau default filter – with several advantages. One of these advantages is that you can define a default period (e.g. 30 days), instead of always starting with the full period.

1.1: Create two calculated fields as default values

Before creating the necessary parameters we create two accompanying fields: ‘startdate – default‘ and ‘enddate – default‘.

I want my dashboard to show by default the last 30 days, so I calculate the date based on that period. The end-date will be by default yesterday.

If you like an alternative period – like ‘last month’ you can use

DATETRUNC('month',DATEADD('month',-1,TODAY()-1)

and

DATETRUNC('month',TODAY()-1)

1.2: Create 2 parameters: start-date and end-date

Next, we create the parameters.

Only difference between these two – beside the name – is the ‘Value when workbook opens’ – which are respectively ‘startdate – default’ and ‘enddate – default’ – the calculations we have just created. If you close the parameter-dialog, the parameter is automatically set to these values.

1.3: Create a ‘set’ as filter

Next step: create a set based on the dates the you want to filter. Right-click on ‘order-date’, select ‘create set’. On ‘condition’ we will use ‘By formula’, and enter the filter-condition:

Now we remove the standard date-filter, and replace it with the newly created set.
This way we created a filter which is much more user-friendly by default!

Remove standard filter, add new set to filter-shelf, add start- and end date to dashboard

Part 2: Make filtering better with predefined date periods

But… we can make it easier for our users. Two date-parameters are not very easy if you want to know ‘last week’, or ‘previous month’. We can need presets for these – and that is not difficult.

2.1: Parameter with predefined periods

Let’s add another parameter: Period Select.

I created 5 options, but use as many as you want. “Last week”, “This Month”, “Previous Month”, “This Year”. And we add a ‘custom’ to make sure every period can be selected, not only the predefined ones.

2.2: Calculated field with predefined periods

Based on this parameter we create a new calculated field which will serve as the new filter. For each defined period we create a filter based on today.
And for the ‘custom’ option we use the same calculation as we used on the set.

Put this field on the Filter-shelf, and set this to ‘TRUE’

If I change the set by the newly created filter-calculation and add the parameter to the dashboard, everyone can easily switch between the periods – and even use a custom date-range!

Using the date-period parameter

Part 3: Show dates only when needed

Functionality-wise very nice – but I don’t like it that both start-date and end-date are always visible, even when a predefined period is selected (and thus only confusing).
Thanks to the ‘Control visibility using value’ feature this is easily fixed. Create a new calculation which returns TRUE only when ‘custom’ is selected:

[Date filter - custom] == 0

And use this the field on ‘Control visibility using value’ on both date-filters, so these parameters only are shown when the ‘custom’ period is selected:

The Result

Now play with the parameters and filters yourself: