Tableau Tip - Comparing a custom date range with the prior year

Here's a quick tip based on a problem that I solved with a colleague this week. Take the following scenario:

You want to see sales for a date range which you can select as you like, and compare that with the same date range one year earlier. And here's the catch - the date range you pick can be over a year. So for example you might select Feb 2015 to Aug 2015 and compare that with Feb 2014 to Aug 2014 OR you might select Jan 2014 to Apr 2015 and compare that with Jan 2013 to Apr 2014. So the periods can overlap.

Here's how we solved it:

To see the sum of sales for the current custom period, use parameters - one for start date and one for end date. In our case all data was registered at month start and we were doing months only. Because of this we didn't want to see the calendar appear so we used a list of dates created in Excel and copy and pasted in to the parameter. Example using Superstore Sales below, note US date format:

And then duplicate the 'Start Date' parameter and name it 'End Date'.

Now what I often like to do when I want to bring back data that meets a certain criteria (in this case fit a date range, but could be 'is within a region' etc...) is create a calculated field that returns the value if the criteria is met and 0 if its not met. This means it can still be aggregated however you like and you don't have to worry about the potential conflict or impact of filtering. So in this case we wrote the following calculated field for the current period sales

Notice how the IF statement is completely inside the aggregation - that's something you can use in many situations including much more complicated calculations. And again in our example, all data was at month level.

Now how about the prior period, one year back? Well because the periods can overlap and can be any period we can't use one of the standard table calculations, at least I don't think so. So we made use of the DATEADD function with a value of 1 to offset the order date by a year, so that we pick up prior data. Like so

A quick line plot shows that the calculations are doing what we expect

And now we can very easily do comparative calculations of current period versus prior period, for example to see % growth across categories

hope you find that useful.