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

Comparisons and viewing trends across dates are two effective ways to turn raw data into insights, but dates can be tricky to work with in Tableau. I illustrated in my last post how to find hidden patterns in line graphs by adding a slope graph toggle, but what if the dates are not lined up on the same axis? For example, if you were to make a sales by continuous Order Date line graph with Tableau’s Sample – Superstore dataset colored by Year, you would get four colored lines that do no not line up on top of each other.

Tableau does not have a Month + Day date part, which can make it challenging to compare year-over-year performance. Due to this, I often go through the relatively elaborate process of setting up two sets of date comparison filters and equalize the dates so the lines are right on top of each other. This post will show you a simpler way to normalize months and days so they share the same axis when colored by year. When the marks line up, it is much easier to evaluate year-over-year performance.

View / Interact / Download

The challenge of not having a month + day date part in Tableau

First, let’s have a look at why this tip is needed. Here is an illustration of the sales by continuous Order Date line graph mentioned in the introduction.

As you can see, while all four years are represented on the line graph, it is difficult to compare the same time period year over year. For example, it is almost impossible to determine if the peaks in each year line up on or around the same dates. This would be much easier to evaluate if the lines for all four years were lined up on top of each other on the same axis.

New to Playfair Data?

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

Before I discovered the tip that I’m about to share, I would go through the manual process of adding the appropriate number of days to each historical year in my dataset to get them to line up with the current year; something like this:

IF YEAR([Order Date]) = 2018 THEN [Order Date]
ELSEIF YEAR([Order Date]) = 2017 THEN [Order Date] + 365
ELSEIF YEAR([Order Date]) = 2016 THEN [Order Date] + 365 + 365
ELSE NULL
END

This approach works, but has a few limitations:

– It requires the user to do the math to properly normalize year-over-year dates
– It does not account for Leap Day
– It is not scalable in that if more years are added, the formula doesn’t automatically account for new dates

Create a free account, or login.

Unlock this tutorial and hundreds of other free visual analytics resources from our expert team.

Already have an account? Sign In

Name

First Last

Email

Password

Enter Password Confirm Password

Phone

This field is for validation purposes and should be left unchanged.

Δ

By continuing, you agree to our Terms of Use and Privacy Policy.

or

---

Continue with Google

Continue with LinkedIn

Explore unlimited access to all offerings.

See membership options.

Equalizing year-over-year dates on the same axis using the MAKEDATE function

An easier way to normalize year-over-year dates is to create a calculated field that combines the Month and Day date parts of each date in your dataset with the current year. This can be achieved using the MAKEDATE function and this formula:

MAKEDATE(2018,MONTH([Order Date]),DAY([Order Date]))

Now if I replace the original Order Date field on the Columns Shelf with my newly created Equalized Date field, the lines for all four years will line up in 2018.

 

Admittedly, this view has too many marks to see much more than the spikes, but you can now filter on the Equalize Date field to display fewer marks.

Make your data more usable with our free newsletter.

Get monthly tips and tutorials:

Email(Required)

Δ

You can also change the date part of the Equalized Date field and everything will still line up year over year. Here’s how the view looks when changing the date part from Day to Quarter.

 

You can even take this a step further by allowing you and your end users to change the date part of the equalized date on the fly – just replace Order Date with Equalized Date in the tutorial.

Thanks for reading,
– Ryan

Access Exclusive Benefits

Dashboard templates, digital credentials, and more.

Learn More