Problem:

A sum of previous year to date Opportunity amounts is needed on Account so that a year over year comparison can be made.

Solution:

Create a formula field to use to filter Opportunities, then use Rollup Helper to sum the amounts.

  • Create the following fields.
    • Create a Formula Field on Opportunity - PYTD (Previous Year to Date)
      • Formula Return Type:  Checkbox
      • Year(CloseDate) = year(today()) -1 

        && 

        if(Month(CloseDate) = Month(today()), Day(CloseDate) <= Day(today()), Month(CloseDate) < Month(today()) 
    • Create a Currency Field on Account - PYTD Revenue
  • Create a rollup with Rollup Helper for Total Closed Won Opportunities PYTD:
    • Rollup Helper Step 1) Select Where You Would Like Your Rollup Results to Reside:  Account
    • Rollup Helper Step 2) Select Target Field:  PYTD Revenue
    • Rollup Helper Step 3) Select Source Field
      • Select Child Object:  Opportunity
      • Relationship:  Account ID - Opportunity.AccountId
      • Which field on Opportunity should we use as a source: Amount
      • Type: Sum
      • Create New Filter
        • Name:  Won PYTD
        • Limit to:  Blank
        • Specify Sort Order:  Default
        • Specify Filter Criteria
          • Deleted = false (default)
          • Won = TRUE
          • PYTD = TRUE
        • Save the Filter
    • Save and Run the Rollup
    • If you haven't already done so, enable real-time on Opportunity.

This use case can be extended by rolling up the current YTD Won Opportunity total to Account for comparison. For further assistance with this use case, schedule a one-on-one demo.

Have any suggestions for furthering this use case or other comparison ideas?  Please post in the comments section below.

 Need help?
Schedule a one-on-one demo

Share