Fun with Date Calculations: Dynamic YTD and Prior Year Comparisons

I have an interesting relationship with date calculations. While they can be extremely frustrating for the most part, I find the challenging ones so satisfying to figure out. They are like little complicated puzzles packaged in what appears to be your average, everyday, mm/dd/yyyy format. At yet, the types of calculations you can do are so much more than what meets the eye.

Comparing Measures for Different Time Periods

Recently I was putting together a dashboard with a common “year to prior year” comparison where the most recent year is a bar and the prior year comparison is a reference line. Something quite similar to Andy Kriebel’s NHL Attendance viz.

So this got me thinking. I wanted me calculation to be dynamic. Often for static data sets you can simply create a calculation that only returns data for a particular year or date.

Example: SUM(IF [Date]=2017 THEN [Sales] END)

I decided I wanted the flexibility in my dashboard to compare two numbers and that these would be dynamic based on the max date (YTD) of the data set. This was so that I could simulate a regularly refreshing data set.

1. The current YTD sales to the prior complete year sales: This would show how something was trending to be above or below the prior year.
2. The current YTD sales to the prior YTD at the same point in time (for example, if the data was through April, then the calculation would compare this year’s sales for Jan-April to last year’s sales from Jan-April).

Max Year & Prior Year Calculations

Using the Superstore data set within Tableau, I began to replicate some of these calculations. The first is isolating the maximum date in the data set. In order to really show how this would work, I limited the data at the data source level to only through April 2018 so that I could simulate working with a partial year.

Then I created the following calculations:

Max Year

{MAX(YEAR([Order Date]))}

This will return the maximum year in the data set regardless of the level of detail. Every row in the data set will return the maximum year, or 2018 in this case.

YTD Sales

SUM(if YEAR([Order Date])=[Max Year] then [Sales] ELSE 0 END)

This will return the Sales measure as long as the year of the order date matches the maximum year.

You can see from the table below how these calculations work.

The prior year calculations are then done the same way.

Prior Year

{ MAX(YEAR([Order Date])) }-1

Prior Year Sales

SUM(IF YEAR([Order Date])=[Prior Year] THEN [Sales] ELSE 0 END)

With these four calculations you can create a max year compared to prior year bar chart with a reference line, which works well for full years. If you had static full years and you wanted to compare you could also just fix the dates as I mentioned above. But, I wanted something dynamic. I wanted to be able to compare the max year to the prior year but also a YTD comparison. Because my data only went through April, my bar chart looked like this below.

Prior YTD Calculations

In order to create a prior YTD calculation, I needed to be able to create a calculation that would only be true if the day/month matches the maximum day/month in my data set.

Prior YTD Sales

SUM(IF YEAR([Order Date])=[Prior Year]
AND DATEPART(‘dayofyear’,[Order Date])<=DATEPART(‘dayofyear’,{MAX([Order Date])})
THEN [Sales]
END)

This calculation will only render as true if the order date was in the prior year and the day of the year is equal to or less than the max day of year for the entire data set.

Setting Up a Parameter & Indicators

So, now we have three calculations we can use: The current/max YTD sales, the prior full year sales, and the prior YTD sales.

I like to create charts that can be dynamic based on the user and the question that user may have. I’d rather have one chart that can do two things than two charts if they don’t need to be seen at the same time. Ultimately, these charts answer two different questions with the same fundamental data and chart type.

In comes parameters. Parameters can help us switch between comparisons measures. In my case, I wanted to be able to flip between comparing the current/max YTD sales to either the prior full year or the prior YTD sales. I also wanted to add in some sort of indicator that would help me assess where we were trending in relation to these two measures.

First, I created my parameter called “Compare to Value” that has two integer values: 1 for Prior Year to Date and 2 for Prior Year.

Secondly, I created another calculation that would use this parameter with the same name.

Compare to Value

CASE [Parameters].[Compare to Value]
WHEN 1 THEN [Prior YTD Sales]
WHEN 2 THEN [Prior Year Sales]
END

Now that I have my dynamic comparative value, I can create my chart. I used the current YTD as my main measure, with the comparison being the Compare to Value I just created above. The result looks like this, though you really just need Category on the Rows (the other pills are for the set action titles).

Comparison Indicators

Lastly, I created two indicator values. The first is a color indicator that will show me if the current/max YTD sales are above or below the comparison value. When placed on the color mark the bars will turn a share of teal when the value is higher than last year.

Increase in Sales

[YTD Sales]>[Compare to Value]

Since the Prior YTD Sales is likely to be above the YTD Sales, especially in the beginning of the year, I also wanted an indicator that would provide some estimate about the future projection since the “Increase in Sales” color indicator is likely to be gray. While this is a “rough” estimate since it doesn’t take into account any seasonality in a data set, it will provide a simple projection based on monthly average.

Basically, I wanted to be able to say, there were 4 months so far in the data set and 4 divided by 12 is 33.3% so we should be approximately 33.3% of the way towards the full prior year’s sales. If the sales is greater than that, then we are projected to be above last year’s sales.

What I decided to do was to create a calculation that gave me a percent of the current YTD to the prior full year and an indicator color that would only show when the comparison target in the parameter was for the prior full year, not the prior YTD since that would show up with the color indicator on the bars.

I created the new percentage calculation as follows:

Percent to Compare Value

[YTD Sales]/[Compare to Value]

And then a Projection calculation that also required I count the number of months in order to accurately divide by 12.

Count of Months

COUNTD(IF { MAX(YEAR([Order Date])) }=YEAR([Order Date])
THEN MONTH([Order Date]) END)

Projection

IF [Parameters].[Compare to Value]=2
AND [Percent to Compare Value]>([Count of Months]*(1/12))
THEN “Projected Above Last Year”
ELSE “Projected Below Last Year”
END

The resulting viz looks like this, below, where you can see that even though when comparing the current YTD to the prior full year for supplies, for example, sales is obviously lower, I can now see in the percent to the prior year that supplies is possibly projected to be higher than last year since after 4 months, it already had 48% of the of the sales from all of the prior year.

Adding Set Actions

Just go to Lindsey Poulter’s Tableau Public page or blog. LOL. I directly got the ideas from her, so there is no sense explaining it here!

Here is the link to my final viz. I hope you find it useful!

 

Cheers,

Lindsay