# Fun with Date Calculations: Dynamic YTD and Prior Year Comparisons

Posted by

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.

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

1. Fred says:

This is impressive! Love the viz, looks pretty polished. I can’t get my head around how you ended up admin the + button and the category text on the click to reveal. Would you mind sharing the steps on how you managed to do that!

Thanks,
Fred

Like

1. Hi Fred! You can download the workbook to see how it works, but bottom line is I’m using Set Actions. The (+) button and category text was learned from Lindsey Poulter (http://www.lindseypoulter.com/). She has some good resources on her blog. I actually just reverse engineered her examples in an excellent Tableau set action workbook you can download from her Tableau Public page.

-Lindsay

Like

1. Hi Lindsay,

A very nice effort on your part and a good, step by step explanation. A question though, how would one tweak the PYTD calculations change if your year was a Fiscal Year with start month of July rather then January? And let’s say for your May year, you had data for up to November only (5 months of data)?

Like

2. Hi Tony! Yes that is certainly possible. In my example, you would create a new “Prior Fiscal Year” calc and then one to give us the Sales:
Prior Fiscal Year: { MAX(YEAR(DATEADD(‘month’, -6,[Order Date]))) }-1
Prior Fiscal Year Sales: SUM(if YEAR(DATEADD(‘month’, -6,[Order Date]))=[Prior Fiscal Year] then [Sales] END)
This will give us values just for the FY from July-June.
For Prior YTD we can use this calculation:
Prior Fiscal YTD Sales: SUM(IF YEAR(DATEADD(‘month’, -6,[Order Date]))=[Prior Fiscal Year]

I hope that helps!
Lindsay

Like

2. Antony Mwangi Wambui says:

Hello Lindsay,
This is awesome! Thank you for your efforts and very immaculate, step by step explanation of the entire process. Question: how would one tweak the PYTD calculations above if the year was a Fiscal Year with a start month of July rather than January and let’s say for the Max Year, you only had data up to November (5 months of data only)? And finally, would you kindly explain the Count of Months calculation–my understanding is that it’s counting the number of months in the Max Year and wondering if one could also use this calculation to achieve the same result: COUNTD(MONTH([Max Year]).
Thank you for you time and insight!
Tony

Like

1. Hi Tony,
In my current example that calculation won’t work because the Max Year field is an integer, not a date. It’s a single value of 2016 (for example), so there are no months to count. However, there often are many solutions to a problem in Tableau. 🙂

Lindsay

Liked by 1 person

1. Antony Mwangi Wambui says:

Thank you Lindsay for taking time to get back to me. I will try and run these calculations using some data that I am working on and I hope that you won’t mind it if I reach out to you for help should I run into a bind. Best!

Like

3. Charan says:

Hi Lindsay,

Here we can compare year to year but can we compare Jan’19 vs Jan’20?
(This year current month vs previous year same month)

Like

1. Absolutely! Depending on your data structure, you could use a parameter to set the month, or the max date if January 2020 is the max date, or fixed value. For example, if we create a parameter with values 1-12, we can create a calc that returns just that selected month.
Comparison Month: DATEPART(‘month’,[Order Date])=[month]

Then create our Max Year calc: { MAX(YEAR([Order Date])) }

Then we can create a calc to return the sales for that month in the max year.
Max Year Comparison Month: IF [Comparison Month] AND YEAR([Order Date])=[Max Year] THEN [Sales] END

Then we create a calc to give the prior year sales for the selected month.
Prior Year Comparison Month: IF [Comparison Month] AND YEAR([Order Date])=[Max Year]-1 THEN [Sales] END

Now both calcs can be used in the same view to compare the month of one year to the prior year.

Like

4. Tony says:

How would I get previous year to date excluding current month. Example: Today is 7/28/20, I would like to get 1/1/19 – 6/30/19.

Like

1. Sure! We can certainly do that. Here we can say, if the year of the order date equals last year (as compared to today) and the day of year is less than or equal to the last day of last month (relative to today) then show the measure (in this case sales). Let me know if this works or not! 🙂 (sorry for the delay in my response – I’ve been swamped and then on vacation)

SUM(IF YEAR([Order Date])=YEAR(TODAY())-1
THEN [Sales]
END)

Liked by 1 person

5. Mark Gandy says:

I realize this is an older post, but great concept and creativity – I’m implementing for a project – thanks!!

Liked by 1 person

1. Paul Triphala says:

Hi Lindsay, A little late to the party —Excellent post! I have been trying to customize this concept with dynamic parameter action. I am actually looking to: 1) Insert a user chosen time period say “Last N months” to return MoM %s instead of MTD numbers….the output would be a as a dynamic line chart with month of order date in the column shelf…comparing sales of two or more sub-category products. I’ve created the N months parameter and the corresponding T|F date filter….but, am stuck on the dynamic month on month change of the measure value (sales / profit etc.)….Any ideas on a solution? Many Thanks!

Like

6. Lindsay,

How can I make a YTD calculation that accounts for the previous year’s date sales but belongs to this year’s product? wise?

I am currently using the following calculated fields, but unfortunately, this one does not account for pre-term sales.

IF DATEPART(‘month’,[Pay Trx Date]) < DATEPART('month',TODAY())
OR
(DATEPART('month',[Pay Trx Date]) = DATEPART('month',TODAY())
AND
DATEPART('day',[Pay Trx Date]) <= DATEPART('day',TODAY()))
THEN "YTD"
ELSE "Future"
END

Any suggestions?
Raul

Like

1. Hi Raul,

Are you filtering for a current year (and therefore a current product) and need to show the prior year’s sales associated with it? You could use an LOD or perhaps (if I understand correctly) just create a new “date” field and spoof the prior year’s sales to be the current year so that if you have a filter on year, the prior year’s sales still attach. I’d probably have to see a sample/mock workbook to really help as I’m very much a visual learner/answerer. 😉

Lindsay

Like