How to create automated date filters in Tableau
- Kaloyan Petkov
- Mar 13
- 3 min read
Setting up date filters via calculated fields is very important asset when working with Tableau Desktop. It is a necessity when trying to automate a dashboard and make it more sustainable. Imagine a revenue dashboard that each day is refreshed with the new sales data. Setting up a view where you don't have to switch the filters every time is a great advantage. So lets see how we can tie up data filters to the most recent date /month. In this way every time most recent data comes the filters will be corrected automatically. Absolutely necessary for BI analysts to know well those applications.
Before we show few examples lets first describe the data sources that we are going to use as example. It is a daily data for revenue of a clothing store chain with several shops. For our current example we only care for the revenue field and also date field that we have converted to a "Month"field using DATETRUNC('month',Date). The objective is to create several date filters based on that field Month that work in fully automatic manner.
One quick tip: in order to test my calculation I usually put the Month field on rows as a Discrete which lets me see all the available data points in the field and test my calculations:
The general idea is to create several boolean calculations based on few use cases that can be used either as filters on sheets or in downstream calcs in order to automate date filtering.
Case 1: Filter only most recent month of data
This is fairly easy exercise. In a field named "Filter LM" (LM coming from Latest Month") input this code:
[Month]={MAX([Month])}
Note that the function MAX() must be in curly brackets {}, because we need a single value (most recent month) for all of the rows in the view. In the end it is a boolean field and putting it on Rows shelve you can verify the True is only for the latest month.
Case 2: Filter only Previous Month of data
Similarly using {MAX()} to find the latest month and then with DATEADD revert back 1 month to get it:
[Month]=DATEADD('month',-1,{MAX([Month])})
Case 3: Filter Same Month Last Year Data
Quite often is required to have the ability to show only data for the exact same month a year ago. In this case we follow the same logic in the first two cases, but also add another statement for the year:
Month([Month])=Month({MAX([Month])})AND YEAR([Month])=year({MAX([Month])})-1
This function will find the latest month and then using DATEADD on the year will revert it back to previous one. Note that here we find the month as in Jan, Feb Mar etc., not like a date format, this is because we are applying it across years.
Case 4: Filter Year-To-Date (YTD) Current Year
In the previous cases we were looking to create a filter for only a particular month, but also sometimes we are required to show all the months until now in the year, called YTD view. The logic is pretty much the same, just that we now use inequality operator "<":
Month([Month])<=Month({MAX([Month])})AND YEAR([Month])=Year({MAX([Month])})
Case 5: Filter YTD Previous Year
Often stakeholders are interested in comparing this year YTD numbers vs same period last year. In order to achieve this we will create similar YTD filter that only points to last year months:
Month([Month])<=Month({MAX([Month])})AND YEAR([Month])=year({MAX([Month])})-1
Note because we are again looking across years so the function "Month" is used, that returns integer equivalent of month, rather than sequential date format. The rest is similar as previous cases.
Practical Example of Use
Might be wondering how we are going to use all those boolean filters that we created? Here is an example. Let's say we want to create a KPI that shows only the revenue in most recent month that we have data. Having the calc from Case 1 it is extremely easy:
Putting it in a IF-statement in another calculation means only data from the latest month will be considered when using this new field. Then simply put it in your view and it is ready. The same logic can be applied for using all the rest of those filter-fields, even several can be combined into single calc.
Tableau Workbook With Examples (Unzip to find the .twbx file)
Data source used for example:
Comments