Save Me from “Last Activity”!

Q: “When was this Salesforce record last worked?”

A: If you mean “when was an Activity last completed for it?”, I got a great solution for you.

But first, make sure NOT to use Salesforce’s built-in “Last Activity” field (API Name: LastActivityDate). It behaves really strangely, like allowing “Last Activity” to occur in the future.¹

(I’ve got a full tirade about it. Once you’re done with that, come on back here to implement a Better Mousetrap.)

1) Create a new Activity Custom Field, with Type “Formula (Date/Time)”, named Date/Time MARKED Complete, with the formula

IF(IsTask,
   CompletedDateTime,
   IF(ActivityDateTime < NOW(),
      ActivityDateTime,
      NULL))

2) Create a new Activity Custom Field, with Type “Formula (Checkbox)”, named “Has Date/Time MARKED Complete?“, with the formula²

NOT(ISBLANK(Date_MARKED_Complete__c))

3) Create a new Activity Custom Field, with Type “Formula (Number(0))”, named Days Since MARKED Complete, with the formula²

IF(Has_Date_Time_MARKED_Complete__c,
   NOW() - Date_Time_MARKED_Complete__c,
   999)

4) In a Report that has both Activities and the object you’re rolling up to (like Accounts), create a Custom Summary Formula (I named it Days Since Last Activity MARKED Complete) with this formula

IF(Activity.Has_Date_Time_MARKED_Complete__c:MAX = 1,
   Activity.Days_Since_MARKED_Complete__c:MIN,
   NULL)

You’re done! The Custom Summary Formula field enables reporting when someone last completed an Activity for an Account, Contact, Opportunity, Lead, Case, etc. . .

“Activities with Accounts” Report, with Custom Summary Field “Days Since Last Activity MARKED Complete”

---

¹
Q: How can Last Activity Date occur in the future?
A: Salesforce Help explains that Last Activity Date is either

• The latest Due Date of the Closed Tasks on a record, or
• The latest Date of Events on a record.

Out-of-box, Salesforce allows both

• completed Tasks to have Due Dates in the future or
• Events to have Dates in the future

. . . so either Tasks or Events can render, say, an Opportunity’s “Last Activity” in the future. wtf.

²
Q: Why are these fields needed?
A: Two reasons:

1. Reports can’t roll dates up to parent records, so we need to convert “Date MARKED Complete” into the numeric “Days Since MARKED Complete”.
2. Reports’ Custom Summary Formulas treat nulls as zeroes, so we need an arbitrary, large error value–I’m using 999 here–to disqualify null “Days Since MARKED Complete” from MIN calculations.