How to Account for Leap Year in Tableau

Three strategies to handle Leap Year in Tableau

Struggling to explain, control, or align data in Tableau because of Leap Year? This video walks through annotations, converting measures to daily rates, and wrangling YOY axes into alignment.

Hi, welcome to Playfair+. I’m Felicia Styer, and today we’ll be talking about Leap Day. Our first method is really straightforward. We’re just going to add an annotation onto our reporting. So here in method one, we can see that we’ve got this report that’s showing the number of hours in February of 2023, versus the number of hours in February 2024. Because we’ve got Leap Day, February 2024 is about 4% higher.

Just to add context to this number, we’re going to add a text box, clarifying where this increase is coming from. I’m going to pull a text box onto my dashboard and paste my warning onto it. I take a floating text object, drop it on my dashboard, and paste my message into it. Then I’ve just added a quick note of context into my report so that my users or anybody looking at this report will see that some of this increase is coming from Leap Day to contextualize the year over year change. For our second method, we’re going to be taking the same chart and we’re going to convert this total into a daily rate. This is going to control for comparing two different population sizes. Here we go into an individual view and we’re going to be replacing this SUM hours mark with a custom calculation to take the average daily value.

I’ve already written this calculation out for us. It’s daily hours calc. Let’s take a look at how this works. Here I’m using INCLUDE, and they include function will aggregate something including the specified dimension. This aggregation is going to always include the date level. For each day, we’re going to take the total number of hours. And then once we have this calculation, we’re going to average it. This will give me my daily hours, then I’ll take the average across of those to get the rate for each time period. Let’s replace SUM of the hours with daily hours. Remember we want to use the average. We have average daily hours here. Now we need to update the labels.

I’m holding control when I drag so that I get a second copy of it. I’m going to take daily hours and apply a table calculation to it to get our year review change. I have my daily hours here. I’m going to take my quick table calculation and find the percent difference. Which is going to be zero. And then I’ll take that and apply it to our label. We have 0%. We can see that there’s 24 hours per day in both years with a 0% change year over year. Obviously, you won’t always be working with hours. You know, maybe it’s visits, maybe it’s clicks or sales. But if we take that cumulative number and we turn it into a ratio, it controls for the different number of days between the two years.

Finally, you can apply a quick format to get rid of those extra zeros. And we’re good. This is what it looks like when we’ve applied the new calculation. Instead of taking our total hours, now we have our hours per day between 2023 and 2024. Then method three is going to be aligning the year over year trend. Here we have a chart that takes 2024 and 2023 and plots them side by side. Here 2024 is in Navy and then 2023 is brought in in gray. And if I apply color to weekday, you’ll see that Mondays are aligned with Mondays.

Tuesdays are aligned with Tuesdays, etc. We’re not mapping March 1st of 2023 onto March 1st of 2024. They’re a little bit offset. So that maybe the first Monday in March is aligned to the first Monday in March. And you’ll see this a lot when we have metrics with high seasonality like hotel check-ins, anything travels, flights, like travel and leisure. You get that high weekday seasonality. Sometimes you want to see year over year plotted like this. What can happen is leap day sometimes creates an offset. So as of leap day, things get offset and you start comparing Thursdays to Fridays. This calculation is going to give you a way to keep those lined up even over weekday or even over leap day. Here’s a quick peek behind the scenes of the calculations. This is what’s going on. We’re taking February 16th of 2023 and February 15th of 2024. And we’ve created a calculation that takes both of those Thursdays and maps them to February 15th of 2024.

And here is a peak effect calculation. The first part of the calculation here is bringing that year forward. We are looking at what year is today in and versus what year is the date in my data in. I’m taking the year of today and I’m taking it all the way back to January 1st. And then I’m adding the days of the day of year to that. So, if today is the 60th day of the year, I would take January 1st of 2024 and add 60 days to it. Finally, we subtract one to control for January 1st. If I’m looking at like January 2nd and it’s the second day of the year and I add two to January 1st, I might up with January 3rd. So, this minus one just takes us back to the same date.

Finally, this second section is going to control for that day of week. This is making sure that our Mondays are Mondays, our Tuesdays are Tuesdays. We take the first day of the current year here. So, this is saying, all right, January 1st, what day of the week was that? Then we look at the first day of the year in our data set.

And so, we look at January 2023, what day of the week was that? We subtract the two to bring 2023 into the correct day of week. That’s the behind the scenes of how the calculation works. It took a while to figure it out, but it’s pretty straightforward. This is what it looks like applied, it becomes our new date axis. And we can plot any of our measures along it, keeping the day of week in line. And then this is what it looks like in practice. So, this has them aligned week over week. It works smoothly over leap day. We can compare week over week between the years. Anyway, I hope this helped and I hope your reporting stays accurate. Thanks for watching. This has been Felicia Styer with Playfair+.