5 custom date functions in Tableau

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!