How to Automatically Compare Date Periods in Tableau

This gives us the current month but we still need our comparison month isolated. This next calculation is going to be very similar to the first but is going to introduce another function called DATEADD. I’ll start by duplicating the “Current Month” field we just created, then right-click on the copy, select Edit, and I’ll rename this field “Comparison Month”.

Now, in our calculated field we are going to insert that DATEADD function in front of our second DATETRUNC with an open parenthesis. Then we will add ‘month’ for our date part, -1 for our interval, and then a comma after that. Don’t forget to add a close parenthesis at the end to close the DATEADD function and change ‘Current Month’ to ‘Comparison Month’. Click OK and you should have the following calculated field:

IF DATETRUNC(‘month’,[Order Date]) = DATEADD(‘month’,-1,DATETRUNC(‘month’,TODAY()))
THEN ‘Comparison Month’
END

What is happening here is we are getting our current month from our DATETRUNC function, then we are essentially subtracting 1 month from the date returned. So, if TODAY() is 7/10/2020 the DATETRUNC will return 7/1/2020 and the DATEADD will subtract 1 month from that and the final value returned will be 6/1/2020.

Now it’s time to combine these together so we can see them in the same view. I’m going to create another calculated field called “Isolated Date Classifications” and enter the following:

IF [Current Month] = “Current Month” THEN “Current Month”
ELSEIF [Comparison Month] = “Comparison Month” THEN “Comparison Month”
ELSE ‘Not in Range’
END

If you prefer, you can consolidate the three calculated fields created to this point into one, but there are at least two advantages to keeping all of the calculations separate:

1. It’s easier to troubleshoot the smaller, individual calculated fields in the case something breaks
2. You may want to use the individual calculated fields for another purpose, which you wouldn’t be able to do if they are all nested together

If you want to isolate current and comparison months with a single calculated field, the formula would be:

IF DATETRUNC(‘month’,[Order Date]) = DATETRUNC(‘month’,TODAY()) THEN ‘Current Month’
ELSEIF DATETRUNC(‘month’,[Order Date]) = DATEADD(‘month’,-1,DATETRUNC(‘month’,TODAY())) THEN ‘Comparison Month’
ELSE ‘Not in Range’
END

Regardless of which option you choose, it’s always a good idea to pause and check your fields to make sure everything is working properly. For quality assurance, I created a dual-axis line graph with Sales from our Sample Superstore dataset and added “Isolated Date Classifications” to the Color Marks Card. This tutorial is being written in the month of July 2020 and you can see July 2020 and June 2020 highlighted there so everything is working how we wanted it to.

How to parameterize the isolated date part

Now let’s look at how to parameterize the date part in the calculations we just created. We will start by creating a new parameter called “Date Part”, change the data type to String, allowable values to List, and add month, week, and day into the list of values. Of course, you can add additional values if they are relevant for your business such as quarter and/or year.

Notice that I left those values lowercase which is very important because the DATETRUNC function only works with lowercase values. However; you can change the Display As section to whatever you want.

Next, we will create another calculated field by right-clicking on our Order Date field, hovering over “Create”, and selecting Calculated Field. I labeled this field “Date with Date Part” and the calculation is going to be:

DATETRUNC([Date Part],[Order Date])

How to Use Parameter Actions to Change Date Parts in Tableau

To test this, I am going to right-click and drag that newly created Date with Date Part calculated field and replace the Order Date field on my dual-axis line graph. When the options of different date parts appear, I am going to choose the first option, “Date with Date Part (Continuous)”. This is because the date part is going to be parameterized; I don’t want to get it stuck on a specific range by selecting a specific date part from those options.

Looks like nothing changed so everything is working correctly. The Date Part parameter is passing in ‘month’ as the value so we still see this month and last month highlighted.

We have a couple other places we need to update to get the parameter controls to work. Recall that in our Current Month and Comparison Month calculated fields we entered ‘month’ as the date part value. We need to go back and edit those fields to now have our Date Part parameter in those values of the functions. We’re also no longer only looking at “Current Month”, so I will change the result to “Current Period”. To make these changes, I will edit those calculated fields to this:

IF DATETRUNC([Date Part],[Order Date]) = DATETRUNC([Date Part],TODAY())
THEN ‘Current Period’
END

IF DATETRUNC([Date Part],[Order Date]) = DATEADD([Date Part],-1,DATETRUNC([Date Part],TODAY()))
THEN ‘Comparison Period’
END

If you’ve made it this far you can really see the value of what these calculations can be used for.

To outline a good example, let’s take the Isolated Date Classification field we created and use it to make a few more calculated fields. We will create calculated fields for the sum of the current period’s sales, the sum of the comparison period’s sales, and the percent difference between them.

SUM(IF [Isolated Date Classifications] = ‘Current Month’ THEN [Sales] END)

SUM(IF [Isolated Date Classifications] = ‘Comparison Month’ THEN [Sales] END)

([Current Period Sales]/[Comparison Period Sales])-1

I’ll create a new sheet and drop these onto the Text Marks Card to show them.

There we have it, the current period, comparison period, and the percent difference. To make it even better these are driven by our Date Part parameter as well!

Until next time,

Ethan Lang
Director, Analytics Engineering
[email protected]