KNOWLEDGE BASE

Calculate Difference in Sequential Dates in One Date Field


Published: 22 Mar 2017
Last Modified Date: 10 Sep 2018

Question

How to calculate the difference in days between a date and the previous date in the same date field. For example, to see how many days are between each order a customer has placed.

Environment

Tableau Desktop

Answer

The below instructions are demonstrated in the packaged workbook downloadable from the right-hand Attachments section of this article.
  1. Drag [Customer Name] to the Rows shelf
  2. Right-click and drag [Order Date] to the Rows shelf
  3. In the Drop Field dialog, select MDY(Order Date) and click OK
  4. Select Analysis > Create Calculated Field
  5. In the Calculated Field dialog box that opens, do the following, and then click OK:
    • Name the calculated field. In this example, the calculated field is named "Days Since Previous Date"
    • In the formula field, create a calculation similar to the following:
    DATEDIFF('day', LOOKUP(MIN([Order Date]),-1), MIN([Order Date]))
    
  6. Drag [Days Since Previous Date] to Text on the Marks card
  7. Right-click [Days Since Previous Date] on the Marks card, and select Edit Table Calculation
  8. In the Table Calculation dialog, do the following:
    • Select Specific Dimensions
    • Check all dimensions
    • For Restarting every, select Customer Name

Additional Information

The DATEDIFF() function will find the number of specified dateparts, in this case days, between two dates. The starting date is LOOKUP(MIN([Order Date]),-1), which will return the date one row previous.

Table functions can be computed differently to allow the calculation to be further customized. In step 8, this example we restart the count for every customer. For more information on table functions can be changed, please see Transform Values with Table Calculations
Did this article resolve the issue?