How to do Dynamic Date Selections in Tableau

Next parameter will be called “Period” and it will be an integer parameter. This parameter will allow you to look at either the current date part or the prior one. In the parameter, type in 0 and -1. 0 will reflect the current date part so I changed the display as field to “This’. -1 will reflect the prior date part so I am using the “Last” as the text in the display as field.

The Last parameter is called “Comparison” and it will allow the user to choose if they want to compare the baseline date part and period selected above to the one immediately before it or do a look at that same range from the previous year.

New to Playfair Data?

Watch our 90-second intro video and receive tips on making your data usable.
 
Watch Video

This parameter will also be an integer parameter with the options of 1 and -1 as the values. -1 represents the prior period and 1 represents year over year. Change the display as field to the corresponding titles to easily identify which is which.

Calculating the dates

Now we can start creating the calculated fields. The first step is identifying which dates fall in the baseline date range. Create a calculated field called Start Date. This calculated field will identify the first day of the selected baseline period. The calculated field is as follows:

DATEADD([Date Selection],[Period],DATETRUNC([Date Selection],TODAY()))

The DATETRUNC function in this calculation is looking at today’s date, and then generating the first date of the date part selected. So it will generate the date for the first day of today’s week, month, quarter, or year. DATEADD will then add 0 to that date if the Period parameter is set to This, or it will subtract one if the selection was Last. That will be the start date.

Now we will calculate the End Date of the baseline date range. The calculation is as follows.

DATEADD(‘day’,-1,DATEADD([Date Selection],1,[Start Date]))

This calculation is taking the Start Date and adding 1 date part to it and then subtracting one day to get the End Date.

Next, we want to create the Start and End Dates for our comparison ranges. First step is to create a calculated field called the Comparison Date Part Multiplier.

IF [Comparison] = 1 THEN
CASE [Date Selection]
WHEN “week” THEN -52
WHEN “month” THEN -12
WHEN “quarter” THEN -4
WHEN “year” THEN -1
END
ELSE 1
END

In this calculated field, when the Comparison parameter is 1 (which is a stand in for Year Over Year), it will return the corresponding integer to the Date Selection parameter. If the Comparison parameter is not 1, then it will return a 1.

Next create another calculated field for the start date of the Comparison date range. This function will take the multiple of the Comparison parameter and the Comparison Date Part Multiplier and add it to the Start Date.

DATEADD([Date Selection], [Comparison]*[Comparison Date Part Multiplier],[Start Date])

The multiple of the Comparison parameter and the Comparison Date Part Multiplier will always be negative, so the Start Date comparison will either be the date for the prior period or the same period in the previous year.

Now create the comparison end date by using the same calculation except replacing Start Date field with End Date.

DATEADD([Date Selection], [Comparison]*[Comparison Date Part Multiplier],[End Date])

The last step is to create a calculated field that will identify whether the Order Date field is in the Current or Comparison Date Range.

IF [Order Date (New)]>=[Start Date] AND [Order Date (New)]<= [End Date] THEN “Current”
ELSEIF [Order Date (New)]>= [Start Date Comparison] AND [Order Date (New)]<= [End Date Comparison] THEN “Comparison”
END

Assemble the dashboard

At this point you have the functionality built out and there are many different ways to utilize it. For my final visual, I am choosing to create a dual-axis line and bar chart. The line represents the current date range’s sum of sales while the gantt bar will be for the prior date range’s sum of sales. I am also going to create labels to call out the start and end dates for both date ranges. Here is the final dashboard:

There you have it, an easy and efficient way to switch between different date ranges and comparison periods.

Stay after it,
Maddie