KNOWLEDGE BASE

DATEDIFF() produces unexpected results


Published: 10 Mar 2016
Last Modified Date: 11 Jan 2017

Issue

When using DATEDIFF() to determine the number of days between dates in Tableau Desktop, some differences give inaccurate results. For example, 9/10/15 - 9/30/15 shows as 40 days instead of 20.
 

Environment

Tableau Desktop

Resolution

Option 1:

Change the aggregation on the DATEDIFF() measure in the view from SUM() to MIN().

Option 2:

Change the DATEDIFF() calculation from a measure to a dimension by dragging it from Measures to Dimensions in the data pane.

Note: See the attached packaged workbook for a comparison of the two options.

Cause

Using the DATEDIFF() function creates a measure by default, and when added to the view it is aggregated as SUM() unless the default aggregation is altered.
Did this article resolve the issue?