In Tableau, DATEDIFF is one of the date functions that help to find the difference between two dates (Date1 and Date2) and returns the integer value based on the difference term (Datepart).
For example, suppose you pass the date_part or difference term as a year. In that case, the Tableau DATEDIFF function returns the difference in the total number of years between Date1 and Date2. This article shows how to use this most popular DATEDIFF function and gives examples of possible date_part terms.
The syntax of the Tableau DATEDIFF function is as shown below. In the below DATEDIFF syntax, the start_of_week argument is optional, and you can use this to specify the day from which the week has to start. For instance, ‘Sunday’, ‘Monday’, etc. The date_part argument accepts year, month, day, quarter, weekday, week, hour, minute, second, etc.
DATEDIFF(date_part, date1, date2, start_of_week)
Remember, the Tableau DATEDIFF function subtracts the date1(2nd arg) expression from date2(3rd arg). So, please be careful; otherwise, you will get negative results.
Tableau DATEDIFF Function Examples
To explain this Tableau DATEDIFF Date function, we use a simple Employee table with the Hire Date column(date1) and the current date as the date2 value. Next, we must create a calculated field to write an expression to use any built-in function. So, right-click the empty space on the Measures shelf and select the Create Calculated Field.. option from the context menu.
Please write the expression below to find the total number of years difference between today and the employee hire date.
DATEDIFF('year', [Hire Date], NOW())
We have also created another calculated field named Today, and we used the NOW() function to get the current date. Next, add Today to the table and YearDiff to the Text shelf to see the difference generated by the Tableau DATEDIFF function.
Similar to the above, we have created another calculated field to find the total number of months difference between the current date and the Hire Date. For more information, refer to Date Functions and other Functions.
DATEDIFF('month', [Hire Date], NOW())
You can create the required calculated fields with different date_part values using this same technique.
Tableau DATEDIFF function using parameter
Instead of creating a separate calculated field for each Datediff expression, we will create a parameter. To do so, right-click the Tableau Measures section empty space and select the Create Parameter option.
It will open the following window. Select the Data type as a String and add a few parameter values representing the date_part, such as year, month, day, week, dayofyear, quarter, and weekday.
Next, create a calculated field with the date_part argument of datediff function as a parameter so that Tableau will replace this parameter with the user-selected value.
DATEDIFF([DatePart], [Hire Date], NOW())
Click the down arrow beside the DatePart parameter and choose the Show Parameter option from the context menu. The below screenshot shows the year difference because that is the default parameter value.
Let me change the Parameter value to Week. The Tableau DATEDIFF function shows the total week difference between HireDate and Now.
