Last week in Houston a student from another Tableau class submitted a question about how to properly create date calculations. For users across the experience spectrum, date calculations can be confusing and frustrating. Often students are intimidated by all of the available functions such as DATEDIFF, DATEPART, and DATEADD. This post will discuss the DATEDIFF function and how it is used to return multiple value-to-date calculations in one view.

I have encountered many clients who have given up looking at multiple date values in one view. Since there are so many ways that businesses can look at change over time, then choosing the appropriate means to create that view can seem daunting. For example, time-series analysis can be discrete or continuous, it can be disaggregated by date or dimension, and it can be filtered in a number of ways; each of these can vastly alter our understanding of data. Choosing the correct method often depends on the question that needs to be answered. Here we will look at different time periods “to date” disaggregated by a dimension other than date. Below is a table calculation that sums the total sales for each month in each year to illustrate the current YTD values.

While this solution may partially solve the problem of visualizing YTD totals, it does not provide a look at single months individually or six month windows for comparison to the prior year. Also, in this view, Tableau would not allow the removal of the date field from the view. Additionally, looking at values by another dimension would only further clutter the view. This table calculation may answer the question but it fails to provide a more flexible analysis. A few simple date calculations and a reference date parameter would allow users to look at multiple date totals.

To do this, first right-click drag the field date into the view. With the right-click drag, Tableau allows the user to choose the date aggregation before the field drops. In this case, choose MY(Month/Year) as seen below. By the end, this will be unnecessary but it will help illustrate how totals are calculated.

Also drop the Sales field into the view as seen below.

The first calculation is Year-to-Date, similar to the table calculation earlier. Some of these calculations will exclude the current month and some will not. This choice will depend on the questions that you need to answer.

In the YTD and Prior YTD calculations I have included the current month. Below I am asking Tableau to return YTD Sales if the current year is the same as reference date and current month is same or earlier than reference date.

The dates are determined by the different date calculations. Since each value will either fall into a range or not, then we will only get the values back within the range we determine. The advantage here is that we can determine a different range for each calculated field and use all of those calculated fields together in one view.

Here are the returned values for the YTD calculation next to the sales values:

To get the previous years’ sales numbers just add another calculated field.  The calculation is only a little different than the original YTD.

Here is the result of the two calculated fields and the actual sales numbers:

The only difference is the DATEDIFF equality is 1 in the prior year instead of 0 in the current year. The DATEDIFF function takes specific parts of our dates (month, day, year, etc.) and gives us the difference between those dates. In the prior year example I only want values back when there is a 1 year difference between my actual date and my reference date and when the reference month is equal to or earlier than the actual month.

To break it down: DATEDIFF (‘year’, [Order Date], [Reference Date]).  Tableau will return a number back which is the difference in date parts (i.e., difference in years, months, or days) from the first date (Order Date) and the second date (Reference Date).  If you wanted to use the current day instead of a user-driven date you could replace today() where you see [Reference Date].

Right now I’ve chosen to disaggregate my sales numbers by Month/Year of Order Date, but if I just wanted to see the total YTD and previous YTD aggregated against the total sales, then I can do that easily by just removing my date field. This action sums all of my returned values in each calculated field which giving me the previous YTD total, current YTD total, and total sales without using any filters or date fields in my view.

I can even choose to disaggregate it by something else like region as seen below.

Those calculations easily solve my YTD/P-YTD problem unless I wanted to do something like add last month’s sales for this year vs. last month’s sales for last year. Also what if I didn’t just want YTD, what if I wanted the previous six month’s total regardless of the year? What if I wanted to see all of these numbers on one view?

Let’s look at using the same idea for previous month as we did for YTD.

It’s the exact same idea, except we’re looking at a previous month instead of a previous year.

For the same month last year, we look 13 months back since we’re not using the current month.

If we wanted current month and current month last year then DATEDIFF’s be equal to 0 and 12.

Finally, let’s take a look at a tricky date calculation. This time we’re going to compare the previous six months this year to the previous six months last year.

You can see I’ve changed the reference date (above) to show that this date calculation overlaps a new year with no issues.

The prior 6-months calculation is just returning values between two dates. In this case those two dates are the greater than and equal to the current month, but less than 6 months ago.  The previous year six months are just as easy, I just need to adjust the number of previous months for which I want to see values.

The best part is that now that I have all these calculations I can put them together on one view disaggregated by region instead of date now.  Even better is that I can use these fields in new and exciting visualizations!