Tableau comes equipped with a few standard date functions such as ‘month of date’, ‘year of date’ and ‘actual date’. You can view the full list of standard date functions here. However, it can be very beneficial to view data over specified periods such as “the last 6 months” or “year to date”.

 

In this blog, I will cover a list of useful custom date functions which will add a lot of value to your dashboards. For simplicity, I will be using Sample Superstore data (accessible in Tableau desktop).

 

Firstly, it is important to create a “dummy today’s date” so you can test whether your date calculations are working correctly. This “dummy today’s date” will simulate different dates as the date of reference. To do that, use the following parameter and subsequent calculation:

 

 

Next, I will go through a couple of date calculations which will use the Pretend date parameter as the point of reference. Think about the Pretend date as a time-machine, it allows you to move back and forth through time to change the date you are looking at the data. Keep in mind that you will not make this date available for the end-user, this is just a parameter which will test the date calculations. When all the date calculations are complete and tested, the Today calc will be changed to TODAY() instead of the Pretend date parameter.

 

To better see if the calculations are working correctly, use them in an IF-statement first and test them on calendar type chart as below. If they work, just remove the IF- statement from the calculation.

 

 

The date is set as 1/8/2019, the ‘Last month’ calculation is set on color and it works! The orange bit is where the IF-statement is TRUE.

 

Let’s get started:

 

1. Last week

 

Truncate the Order Date to weeks:

 

 

Use the DATEDIFF function to calculate the difference between the order week and the set date:

 

2. Last month

 

Create a Month order date function by clicking the arrow next to Order Date, select create and choose Custom Date. Set up the custom date as below.

 

 

Now we can create the ‘last month function’.

 

 

3. Last quarter

 

Make a custom date from Order Date, like before, for quarters.

 

 

Create the ‘last quarter’ function using this new custom date.

 

 

Follow the same instructions to create a ‘last year’ function.

 

4. Last day of last month

 

This function is a little trickier and there are multiple ways to achieve the same result. To get the last day of last month, try using one of the following calculations:

 

 

OR

 

 

 

5. Month to date

 

The next calculation wants to see results from the beginning of the current month to the chosen date (pretend date).

 

 

Use the same method to get ‘year to date’ function.

 

There are many more custom dates to create, for example, fiscal year to date, which can be very useful in Tableau. Play around with some extra custom dates and test them by using it as IF statements on a calendar type chart.

 

Keep an eye out for more fun blogs here!

Charisma Adlem
Author: Charisma Adlem

Charisma has an interesting background in animal science, having completed a Master’s degree (MSc) in Zoology at the University of Pretoria, South Africa. She found her passion for data analytics through her scientific studies. She was delighted to discover that The Data School provides a means to follow her heart and enter a career in data analytics. Charisma is a loving mother of two ferrets and has discovered a talent for abstract and realism painting in her spare time. If Charisma had to choose only one food type to eat for the rest of her life, it would be sushi. Charisma also enjoys outdoor activities including fishing, camping and hiking.