KNOWLEDGE BASE

Calculate Difference in Dates with One Date Field


Published: 22 Mar 2017
Last Modified Date: 22 Mar 2017

Question

How to calculate the difference between dates with only one date field. 

Environment

Tableau Desktop

Answer

Calculate the difference between each date and a constant reference date, then use a table calculation to show the difference between dates in your date field.
This example uses Superstore - Sample data.  
Note: You can choose any date you like for a reference date, as long as all the dates in your data are later than the reference date. 

Step 1: Create a calculated field for a reference date

  1. Select Analysis > Create Calculated Field
  2. Name the field Reference Date, enter the following formula, then click OK
    DATE("1/1/1900") 

Step 2: Create a calculated field for difference in dates 

  1. Select Analysis > Create Calculated Field
  2. Name the field Difference in Dates, enter the following formula, then click OK
    DATEDIFF("day", MAX([Reference Date]), MAX([Order Date])) 
Note: You can choose any date part you like in place of "day". For more information, see Date Functions.

Step 3: Create the View

  1. Place Date on Rows
  2. Place Difference in Dates on Text
  3. Right-click Year(Date) and select More > Custom.
  4. Select Month / Day / Year, then click OK
  5. Right-click Difference in Dates on the Marks card and select Quick Table Calculation > Difference
You will now see the difference in days between each date on the view. 

(Optional) Step 4: Filter the view 

Especially in data sources, like Superstore, that have few gaps in the data, filtering this view can make it more interesting and really show the technique working. 
  1. Drag Order Date to Filters. 
  2. Choose Month / Day / Year.
  3. Choose Select from List
  4. Filter the data to only a few dates and see the numbers change. 
Screenshot of a Tableau View with MDY(Order Date) on Filters and Rows.
 
Did this article resolve the issue?