How to do Dynamic Date Selections in Tableau

In a lot of business cases, you are doing analysis based on common units of time such as: weeks, months, quarters, and years. You may also be asked to compare its performance from previous periods. For example, you may need to analyze current quarter’s performance compared to the previous. Or possibly you might want to see a year-over-year comparison for the month of July. In this blog, I am going to show you how to create dynamic date selections in Tableau to help create ease when switching common date units while also giving you flexibility in choosing to compare to the previous period or the same period last year.

How to Dynamically Change Date Parts in Tableau

View / Interact / Download

Creating the parameters

The first thing to do is create three parameters. The first parameter is going to be called “Date Part” and it will be a string parameter. This parameter is going to be used to determine what date part we want to use in our analysis. For values type in week, month, quarter and year. It is important that the values are lower case as we will use this parameter later in a calculated field that is case sensitive.

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.

Live Tableau Training
May 14 – 17, 2024

Learn directly from industry-leading experts, access hundreds of post-training resources, and more.

Book Your Spot

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

Receive More Free Tips & Tricks Like This Every Week

Let’s stay in touch:

Email(Required)

Δ

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