How to Set Tableau Dashboard Filters to Automatically Update to the Most Recent Date

Data

How to Set Tableau Dashboard Filters to Automatically Update to the Most Recent Date

So, earlier, you created a great report showing sales for the month and published it out to the server. The next month, your manager comes back and complains that the report is set to show last month’s data. Why doesn’t the report just update to the most recent month when the extract is refreshed?

This a familiar topic in the Tableau community, and you can find some of the many different workarounds people have come up with here and here.

Below is an example of what I like to think is one of the simpler workarounds available. Download the attached files and update the end date in the Excel file to test it out. This solution will not meet the needs of every possible use case, and it does not give you a familiar drop-down filter to select dates from. Feel free to comment with the workaround to this problem that you prefer.

Below is an image of the worksheet that I want to have updated when the extract is refreshed. Currently, the date range is set by a discrete filter on month and year:

Worksheet

First, in the worksheet, add the date variable to the Rows shelf, set the display level to show the date part you need in discrete format and remove the date filter. In this example, I am using the month date part so the chart is now displayed for each month.

First changes

Next, create a calculated field with the formula LAST()=0 to filter out all but the most recent month with the default table calculation set to run off of the date variable.

Table Calc

Add this new variable as a filter set to True. If this does not filter out all but the most recent month, go back and adjust the table calculation.

Adjusted worksheet

The worksheet now automatically updates to the most recent month in the extract when opened, but your manager also wants the option to review historical sales for the same month last year. If you add in a discrete date filter now, it will lock the worksheet to the month currently selected when the extract is refreshed. To maintain the automatic updates, add the date variable as a Filter > Range of dates > Special > All dates. Now, the table will update to the most recent date inside of the date filters range.

Filter Field [Date]

Filter [Date]

*** DISCLAIMER (Read this next paragraph closely or you will be doomed to failure)

Once the date range has been adjusted in the filter, the worksheet will no longer update to the most recent date when opened. Before saving a file with this kind of filter, always edit the filter and check that it is set to Special > All dates. Similarly custom views saved on Tableau Server with the date range adjusted will not update when the extract is refreshed but the original view will.

Now, your manager can use the slider, type in the date or click on the date and use the calendar to adjust the time period shown in the chart. The date filter can be customized to hide the slider or if users only need to see the results as month to date the start date can be hidden by a blank text box to clean up the display. The hard part is convincing the manager that entering a date or selecting it from a calendar is easier than scrolling through a giant drop-down list of every day in the last few years.

Calendar                                                             

Calendar

Drop-Down         

Drop-Down

Attached below is a zip file containing the workbook used in this post and an Excel file that generates data for the workbook. To test that the month displayed in the workbook updates automatically, open up the Excel file, change the End Date and save the file.

Change End Date 

Now, when the workbook is opened, it will update to the most recent month. If the workbook is already open, go to a worksheet, right-click on the data source and select Refresh. This will not work if the date filter was adjusted before the refresh or the file was saved after the date filter was adjusted.

More About the Author

Kent Sloan

Analytics Consultant
Basket Analysis Using Parameter Actions and LOD Calculations The initial request that led me to create this dashboard came from a client working in the sales department of a manufacturing company. ...
The 2019 Gartner BI Magic Quadrant Visualized in Tableau Not too long ago, Gartner released their updated Magic BI Quadrant for 2019. This quadrant ranks the industries top BI platforms on ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072