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
      • DATE(YEAR( CloseDate ), 01, 01) < DATE(YEAR(TODAY()), 01, 01) && DATE(YEAR(  TODAY()  ), MONTH( CloseDate ), 01)  <=  DATE(YEAR(TODAY()), MONTH(TODAY()), 01)  &&  CloseDate  > DATE(YEAR(TODAY()-365), 01, 01)
      • For Leap Years, adjust the formula as follows:
        • DATE(YEAR( CloseDate ), 01, 01) < DATE(YEAR(TODAY()), 01, 01) && DATE(YEAR( TODAY() ), MONTH( CloseDate ), 01) <= DATE(YEAR(TODAY()), MONTH(TODAY()), 01) && CloseDate > DATE(YEAR(TODAY()-366), 01, 01)
    • 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.