How to Default to the Most Recent Dynamic Max Date in Tableau

Next, create a calculation that returns the current date using TODAY().

Now we can build a date parameter that defaults to the current date. 

The core of this date filter calculation is {Max([Order Date] = [Selected Date])}. This section will check whether any Order Date values match the date parameter. If there is a match, then the aggregation will be true. The curly braces turn the aggregation into a Fixed LOD, running the aggregation across all rows in context.

The IF statement returns the parameter value when there is a relevant matching Order Date. When there’s no match, it instead returns the maximum Order Date value in context. Whichever date is chosen is then compared to [Order Date].

Adding [Returned] and [Regional Manager] filters to context is critical because it controls how this calculation aggregates the parameter comparison and max date sections. 

After building the calculation, test it out by adding it to the Columns shelf of your worksheet. One date should always be true, and the rest false, even as the manager filter is changed. If the date parameter is set to a date in the view, that date should become the TRUE date. Finally, add [Max Relevant Value] to the Filters shelf and include TRUE values only.

Setting up the UX

Now that the filter is working, let’s set up the UX. Our goal is to assemble a dropdown menu of date values using a list of relevant dates, and a frame displaying the date displayed alongside a button to show the list.

To create the list of relevant dates, duplicate your main worksheet and remove all dimensions except [Order Date] from the Rows shelf. Remove [Max Relevant Value] from the Filters shelf to show all relevant values. Then sort the Order Date values in descending order and hide the header. 

Create a third sheet for the dropdown frame. Just like the original returns list sheet, apply the [Returned], [Regional Manager], and [Max Relevant Value] filters. Create two Marks cards by adding Min(-5) and Min(1) to the Columns shelf. The first Marks card, Min(-5), will be our displayed date value and the second will be the button to reveal the date list. Create a synchronized dual axis and fix the axis range to -8 and 2.  On the first Marks card, set the Mark type to Text and add [Order Date] to the Label property. On the second, set the Mark type to Shape and change the mark color to something neutral. Finally, format the gridlines, zero line, and borders so that the sheet resembles a dropdown menu.

Now that the sheets have been built, we’ll lay the groundwork for a parameter action to show and hide the date list. First set up a Boolean parameter. When the parameter is TRUE, the list will be shown, and when it is FALSE, the list will be hidden. 

Next, we’ll create a calculation that returns the opposite value of the parameter, using NOT [Parameter Name]. 

Add this calculation to the Detail property of the second Marks card of the Frame worksheet, and the Detail property of the Date List worksheet. While the parameter is set to TRUE, add the calculation to the Filters shelf of the Date List worksheet and set the filter to include FALSE only. Test the setup by changing the parameter value. The sheet should become empty when the parameter is FALSE.

Setup the dashboard

Bring all three sheets onto your dashboard, positioning the Date List below the Dropdown Frame. Add the Regional Manager filter control if it is not already present. Apply the filter to all sheets on the dashboard to ensure that all three sheets are focused on the selected manager.

Set up a parameter action using both the Frame and Date List as source sheets, [Show/Hide (input)] as the input value, and the [Show/Hide] parameter as the target. 

Finally, create a second parameter action using the Date List as the source sheet, [Order Date] as the source field, and [Date Selected] as the target parameter. 

Test the actions by clicking on the frame to show and hide the date list, and clicking on a date in the date list to update the display date and hide the list. Now that the dashboard is complete we can see the whole thing in action! As the Regional Manager filter is updated the displayed date changes. The view never goes empty – if the parameter date is not relevant to the manager the most recent relevant date will be displayed instead. Even if the dataset is updated, it will always open to the most recent date. 

This approach is a great addition to row-level security. If the Regional Manager filter was a user filter instead, whenever a manager opened the workbook, it would automatically load their most recent date, and the dropdown would only show relevant dates. Using a standard filter or parameter control could get the same effect for the manager with the most recent record, but all other managers would see an empty page until they select a relevant date value. 

Thanks for reading,
Felicia