Happy New Year! Your Dashboard is Broken…

How to use dynamic date calculations to avoid common filtering Pains

The following blog by Jacqui Moore was originally published on Do Mo(o)re With Data December 2, 2022 and is cross-posted here with permission. Jacqui Tableau Social Ambassador and a Senior Data Analytics and Viz Consultant for Cleartelligence.

Hi Jacqui, Hope you had a Happy New Year! Can you please look at <the super important dashboard>? It seems to be broken. Everything is blank… Thanks!

Have you ever come into the office on the first day of the new year, and found that your dashboards are blank, broken, or still looking at last year? Don’t worry. You’re in good company. But, it doesn’t have to be that way. Using calculations, you can avoid some of the issues that can happen at the start of a new period.

The Challenge

When you have dashboards or views that filter on a specific year, or the current year and prior year, you will need to update filters, and colors, and hide previous years when the new year rolls around.

I have an example dashboard here. A simple dashboard showing the current year and previous year, with YoY Growth, and a monthly trend chart:

When the new year rolls around, it’s going to have new colors, and my YoY Growth sheet is going to need to be updated. I used a relative date filter, but if I had hard-coded the year in filters or calculations, that would need to be updated as well.

The Solution:

Rather than using the date field in your views, you can use calculations to ensure your rollover to the new year goes smoothly.

If I use a calculated field to determine the current and prior year, I avoid the issues above.

• Create a calculation called “Period”

//Period IF DATEDIFF('year',[Ship Date],TODAY()) = 0 THEN 'Current Year' ELSEIF DATEDIFF('year',[Ship Date],TODAY()) = 1 THEN 'Prior Year' END

• Replace anywhere you are using the year with this new calculation. In my example, I’ve replaced the Color, and the Filter to use the “Period” calculation.

The dashboard looks the same, but now, when the year rolls over, I don’t need to make any updates. Without making any changes, my dashboard has rolled over to 2023 seamlessly.

Now, it is possible that your stakeholders would like to see the previous year until the first month of the new year is complete. To do this, we just need to incorporate a lag into our calculation.

There are several ways to approach this, depending on what kind of lag you want to include. Here, I’m saying, if the month is January, then I want to keep looking at the prior two years, otherwise, I want to look at the current year and prior year.

//Period With January Lag
IF MONTH([Current Date]) = 1 THEN 
    IF DATEDIFF('year',[Ship Date],[Current Date]) = 1 THEN 'Current Year'
    ELSEIF DATEDIFF('year',[Ship Date],[Current Date]) = 2 THEN 'Prior Year'
    END
ELSE
    IF DATEDIFF('year',[Ship Date],[Current Date]) = 0 THEN 'Current Year'
    ELSEIF DATEDIFF('year',[Ship Date],[Current Date]) = 1 THEN 'Prior Year'
    END
END

Now, if the current date is in January, it will still show me the previous two years. This prevents the blank dashboard when you arrive on January 2nd.

On February 1st, my dashboard will roll over seamlessly:

In addition, we can solve for a couple of other issues you may have.

If your analysis is for Year to Date (YTD):

We can modify this calculation to handle YTD filters, by adding a second part to the prior year calculation:

//Period To Date
IF DATEDIFF('year',[Ship Date],[Current Date]) = 0 THEN 'Current Year'
ELSEIF DATEDIFF('year',[Ship Date],[Current Date]) = 1 
    AND [Ship Date]<=DATEADD('year',-1,[Current Date]) 
THEN 'Prior Year'
END

We will end up with a dashboard that will always compare Current YTD to Prior YTD. This can also be combined with the lag logic from earlier.

If you only want to show the last COMPLETE month:

Often we will see the trend line taking a deep dive when a new month starts:

This can be avoided by setting up a lag, so you are looking at only the last complete month. We do this using DATETRUNC.

//Period with Complete Month Lag
IF DATEDIFF('year',[Ship Date],[Current Date]) = 0 
    AND [Ship Date]<DATETRUNC('month',[Current Date])
    THEN 'Current Year'
ELSEIF DATEDIFF('year',[Ship Date],[Current Date]) = 1 
    AND [Ship Date]<DATEADD('year',-1,DATETRUNC('month',[Current Date])) 
THEN 'Prior Year'
END

Now, we won’t see the line drop at the start of a new month, and we won’t see a blank dashboard on day one of the new year.

These are not the only way to perform these calculations. They may not even be the best way to write the calculation. However, you can take the concepts of these calculations, and apply them to a number of use cases, including:

• Showing the last complete week, or month

• Showing comparisons of specific time frames, such as last 30 days vs. prior 30 days.

For more of this, and so many other date calculations, check out this post over on the Flerlage Twins site.

Will Perkins also did a great presentation on use cases for DATEDIFF, which is also a great one to watch!

Now, go forth, and enjoy the last time you will spend the first week of the new year updating your dashboards!