Today I want to show you how to use date functions within your Tableau dashboards. Before I kick off you might be thinking “what are date functions”? Date functions allow date calculations to be computed. There are many types of date functions, however for a detailed list please refer to them here. There are many use cases, such as date/time conversion, string data to dates and more commonly date/time difference i.e time difference in days perhaps. This blog post will aim to show you how to use them as reference points. Sometimes you want to refer to or compare previous points, i.e yesterday, last month, last six months or even last financial year. Currently, there are no default options to compare previous points. To compare previous points, you will first need to establish those date references. Luckily this is not a difficult process and I will show you the steps I used to create them.

1. Create a “Today” parameter.
This will act as a date reference point. It will allow you to change dates for the future and potentially act as a point of comparison.
blog9 picture 1

2. Create a “Select Timeframe” parameter
This will allow you to choose and change the timeframe
blog9 picture 2

3. Create Calculated fields based on the Timeframes
For each timeframe you need, you will need to create the respective calculations. I have them below for your reference. For my example, I used Sample-Superstore Dataset, so [Order Date] was the date data. So for future use, you will need to change [Order Date] to your respective date data.

Last Month: DATETRUNC('month',[Order Date]) = DATETRUNC('month', DATEADD('month',-1, [Pretend Today]))
Last Day of Last Month: DATETRUNC('day',[Order Date]) = DATEADD('day',-1, DATETRUNC('month', [Pretend Today]))
Month to Date: DATETRUNC( 'month', [Order Date]) = DATETRUNC('month', [Pretend Today]) AND [Order Date] <= [Pretend Today]
Last 30 Days: DATETRUNC('day',[Order Date]) > DATETRUNC('day', DATEADD('day',-30, [Pretend Today])) AND [Order Date] <= DATETRUNC('day',[Pretend Today])
Calendar Year to Date: DATETRUNC( 'year', [Order Date]) = DATETRUNC('year', [Pretend Today]) AND [Order Date] <= [Pretend Today]
Last Quarter: DATETRUNC('quarter',[Order Date]) = DATETRUNC('quarter', DATEADD('quarter',-1, [Pretend Today])) AND [Order Date] <= DATEADD('quarter',-1,[Pretend Today])
This Quarter: DATETRUNC( 'quarter', [Order Date]) = DATETRUNC('quarter', [Pretend Today]) AND [Order Date] <= [Pretend Today]
Last Week: DATETRUNC('week',[Order Date]) = DATETRUNC('week', DATEADD('week',-1, [Pretend Today])) AND [Order Date] <= DATEADD('week',-1,[Pretend Today])
Yesterday: DATETRUNC('day',[Order Date]) = DATETRUNC('day', DATEADD('day',-1, [Pretend Today])) AND [Order Date] <= DATEADD('day',-1,[Pretend Today])
Financial Year to Date: DATETRUNC('year',DATEADD('month',6,[Order Date]))=DATETRUNC('year', DATEADD('month',6, [Pretend Today])) AND [Order Date] <= [Pretend Today]
Custom Date Range: [Order Date] >= [Custom Date From] and [Order Date] <= [Custom Date to]

4. Create a calculated field that is based on Select Timeframe.
We will need to make one more calculation for the parameter Select Timeframe. These calculated fields aim to bring everything together. This will allow the value or data to be changed respectively to the timeframe selected.
blog9 picture 3.

5. Have fun and use it accordingly.
Bringing that all together we can present data via the date reference points we made. I hope this helps in your data stories!

Anthony Wong
Author: Anthony Wong