By: Eric Parker
Eric Parker lives in Seattle and has been teaching Tableau and Alteryx for 5 years. He's helped thousands of students solve their most pressing problems. If you have a question, feel free to reach out to him directly via email.
If you’ve been reading the blog for a while, you know we’ve covered a number of date calculations including Year to Date vs. Previous Year to Date. 2020 brings a special challenge to calculations like this because it’s a leap year.
*For the sake of the examples in this post, I hardcoded the date for today as 3-14-2020 unless otherwise specified.
If we followed the common approach to creating a Year to Date calculation it would look something like this:
And the common Previous Year to Date calculation would look like this:
If you look closely at that second calculation, you probably already know the issue. Generally, March 1st is the 60th day of the year, however, during leap years, February 29th is the 60th day of the year. That means when this year or last year are leap years, year to date comparisons are going to be off by a day from the end of February forward.
Here’s the issue in visual form.
Data through March 14th this year is being compared to data through March 15th last year.
There are two primary issues here we are going to need to address:
● Leap years have 366 days, standard years have 365.
● We don’t need to account for the extra day of the year from the leap year until the 60th day of the year (February 29th/March 1st).
The first thing I’m going to do is rename the previous PYTD calculation to “PYTD Sales - Standard”.
Next, I will create an alternate PYTD calculation that can be used when 2020 is either this year or last year. I call this calculation “PYTD Sales - Leap Year Alternate”.
See the calculation again below but this time with line-by-line commentary added.
We’ve now addressed the day of year issue, but this calculation is going to flop when Today’s date is before 2-29 or 3-1.
Evidence where today’s date is set to 2-14-2020:
You can see that again, the comparison is off by a day.
The last piece of the puzzle to create a calculation that determines what year it is and if today’s date is before or after the 60th day of the year. Once that’s determined, this calculation will output the correct PYTD calculation for comparison.
Here it is when today’s date is after February 29th.
Here it is when today’s date is before February 29th.
The only thing I can think to add is, this doesn’t currently account for other leap years (i.e. 2024). You can either change the hard-coding, add multiple criteria (i.e. YEAR(“Today”) = 2020 OR YEAR(‘Today”) = 2024), or even create a calculation that determines if the year of this year or last year is equally divisible by 4. That sounds like a problem to solve on another day.
Thanks for reading and if you need help with your own calculations, feel free to reach out or book an office hour!