Brace yourselves – 2024 is a leap year! Here are a few strategies to keep your Tableau workbooks accurate this February. This year will have 29 days in February instead of 28, which is a 3.6% increase in time. When we make year over year comparisons, some measures will be artificially inflated. In this tutorial, I will show you three methods to account for the 3.6% increase in time caused by leap year in Tableau. 

View / Interact / Download

 

Account for leap year in Tableau with annotations

Ratios and averages, like Profit Ratio and Daily Visits, automatically account for the extra leap year day. Summed measures, like Profit and Sales, may be amplified by the extra day. The easiest way to account for this is to add an annotation to your report. 

New to Playfair Data?

Watch our 90-second intro video and receive tips on making your data usable.
 
Watch Video

In the screenshot below, I’ve plotted the YOY hours for February. The chart shows that February 2023 had 672 hours, while February 2024 has 696 hours – a 3.6% increase! It also includes an annotation explaining that we can expect a 3.6% increase in hours on account of Leap Day. A simple annotation reminds users that a February shift could be partially attributed to leap year.

Annotate the impact of leap year in Tableau

Practical Tableau: 3 Ways to Add Alerts to Your Dashboards

 

Use daily averages 

The next solution for wrangling additive measures in YOY comparisons is replacing the monthly total with a daily average. In the example below, I’ve taken the chart from before and converted Total Hours to Avg Daily Hours. 

Use ratios or averages instead of additive measures to eliminate the impact of leap year in Tableau

Obviously, this version does a much better job of controlling for leap year in Tableau! Now we immediately see that both years averaged 24 hours per day. To do this, first I used Include to create a daily total. { INCLUDE [Date] : SUM( [Hours] ) } Then in the view I aggregated the calculated field using AVG() instead of SUM(). 

Compute daily average

Tableau Fundamentals: An Introduction to Level of Detail Expressions

 

Use Day of Year to align weekdays

If your data has strong weekday seasonality, you probably plot weeks year over year. In a standard year, it makes sense to take your original dates, add years to bring it into the current year, and then add days to adjust for day of week. There are a few ways to do this. A basic way is to check the year value and add 364 days to the prior year. A more resilient method computes the year adjustment and day of week adjustment needed for each date, and then brings those dates forward into the current year.

Calculate the adjusted present date for past years

//Adjust Year
DATE(DATEADD(‘year’,
DATEDIFF(‘year’, [Date], TODAY())
, [Date]))

//Adjust DOW
+ DATEPART(‘weekday’, DATETRUNC(‘year’, [Date]) )
– DATEPART(‘weekday’, DATETRUNC(‘year’, TODAY()) )

This dynamic calculation avoids hardcoding and works for any past year, but fails to address leap year in Tableau. After February 29th, the weekdays become misaligned. In this example, I plot attendance with a weekly pattern that is significantly higher on weekdays, peaking on Wednesdays. This plot focuses on February 15 through March 15, with historical records adjusted to align weekdays. Here, February 15, 2024, is compared to February 16, 2023, because both are Thursdays. When I use a Year + DOW (Day of Week) adjustment, Wednesday, March 1st, gets paired with Thursday, February 29th, and the weekdays of each subsequent date becomes unaligned! This causes a new seasonality in the YOY comparison.

How to Equalize Year Over Year Dates on One Axis in Tableau

YOY weekly trend misaligned by leap year in Tableau

Instead, we can use the Date Part ‘dayofyear’ to avoid this issue. My calculation finds the first day of the current year, finds the Day of Year value for the original date value, and then adds the two together. Then we subtract one because January 1st is Day 1 of the year not Day 0. Then it adjusts for day of week by comparing the weekdays for January 1st of the current year versus the year of the original date.

Calculate the adjusted present date for past years, with Leap year controls

//Adjust Year

DATE(DATETRUNC(‘year’, TODAY())
+ DATEPART(‘dayofyear’, [Date])
-1)

//Adjust DOW
+ DATEPART(‘weekday’, DATETRUNC(‘year’, [Date]) )
– DATEPART(‘weekday’, DATETRUNC(‘year’, TODAY()) )

Receive More Free Tips & Tricks Like This Every Week

Let’s stay in touch:

Playfair+Envelope

When we apply this calculation to the plot, we can see that Wednesday, March 1st, gets paired with Wednesday, February 28th, and the weekday values after February 29th remain in alignment. 

YOY weekly trend, leap year compatible

Good luck this February! I hope these strategies help keep your reports accurate this leap year.

Thanks for reading,
Felicia

Related Content

Felicia Styer

Three strategies to handle Leap Year in Tableau Struggling to explain, control, or align data in Tableau because of Leap…

Ryan Sleeper

Automatically Compare the Last Two Partial Weeks or Months Ryan shares the three calculated fields required to automatically compare the…