Loading
Upcoming Mandatory Changes to Public Key Infrastructure (PKI)Read More
Salesforce Enforces New Security Requirements in Summer 2026Read More

Calculate Difference in Sequential Dates in One Date Field

Publish Date: Feb 27, 2025
Task
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.
Steps
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 Resources

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.

Knowledge Article Number

001458030

Attachments

days since previous date.twbx

1206 KB

 
Loading
Salesforce Help | Article