How to Normalize Current and Prior Dates on the Same Axis in Tableau

Create the date range parameters

To start, create two parameters with a data type of Date; one parameter will be used to control the beginning of the date range and the other will be used to control the end of the date range.

For my Start Date parameter, I will set the current value to the first of October and leave the allowable values as all.

For the End Date parameter, the current value will be set at October 31^st and again I’ll leave the allowable values set to all.

Compute the days in the comparison range

Next, we need a calculated field that computes the number of days in the range selected between the Start Date and End Date. This will be used later to equalize the dates from the comparison period so they line up with the dates of the current period. The formula is:

DATEDIFF(‘day’,[Start Date],[End Date])+1

The next calculation we need will determine whether each date is in our current date range or our comparison date range. The formula is:

IF [Order Date] >= [Start Date] AND [Order Date] <= [End Date] THEN “Current”
ELSEIF [Order Date] >= [Start Date] – [Days in Range] AND [Order Date] <= [End Date] – [Days in Range] THEN “Comparison”
ELSE “Not in Range”
END

When the dates are within the Start Date and End Date parameter values, this calculation classifies those dates as the current range. To compute which dates are in the comparison period, the calculation takes the values set in the two parameters minus the days in the range; creating a date range with the same number of days immediately preceding the selected range! The ELSE statement at the end acts as a catch all that will classify dates that did not meet either of the first two statements; we will filter these out later anyway.

The last calculated field we need will add the number of days in the range to the comparison date range so that they line up on the same axis with the selected range. I call this the Date Equalizer and the formula is:

IF [Current / Comparison] = “Comparison” THEN [Order Date] + [Days in Range]
ELSE [Order Date]
END

Create the visualization with normalized dates

We’re now ready to make the chart.

To illustrate what our calculations are doing, I’ll start this new version of the line graph by placing the Current / Comparison calculated field on the Color Marks Card and show the parameter controls for both the Start Date and End Date parameters. As you can see, the dates within the current selection are colored one thing, and the dates in the range immediately preceding the selection are colored a second hue.

And here’s exactly what I was talking about regarding why this hard to compare period over period dates. It’s nice to see the different colors for each date range, but they are not lined up on X-axis.

One of the interesting aspects of this approach is that we no longer need a date filter. The Current / Comparison calculated field is now determining which dates are left on the view, so I’ll remove the original Order Date filter from the Filters Shelf.

We will temporarily see all of the dates in the dataset, with only our current and comparison date ranges colored. We can filter out the dates that are not in range by placing the Current / Comparison calculated field on the Filters Shelf and excluding “Not in Range”.

Lastly, to move the comparison dates forward on the X-axis so they line up with the dates in the selected range, we need to use the newly created Date Equalizer calculated field instead of the original Order Date field. Here’s how the chart looks after replacing Order Date on the Columns Shelf with the Date Equalizer field.

Now the comparison date range has not only been computed, but the number of days in the selected range has been added to the comparison range. This moves the comparison points forward so they line up perfectly on the same axis with the selected range. Plus, the comparison date range is the same number of days as the selected date range. Most companies would look at October compared to September, but they’re comparing 31 days to 30 days. In this view, we’re seeing all of October compared to September plus the last day in August to make the date ranges equal.

This approach even works with different date parts and regardless of what date range is selected. Here’s the same view after changing the date part to continuous week and changing the selected range to Q4 2018 (through 12/29 to filter out partial weeks).

We’ve just created a quarter-over-quarter comparison, and these calculated fields have equalized the dates so we can easily compare the same weeks quarter over quarter on one axis!

Thanks for reading,
– Ryan