Opportunity Line Item Schedule Rollups

Problem

We put in product dates for our Opportunity Line Items and also use Scheduling for quantity and revenue (typically repeating monthly revenue). Our opportunity records capture products for an annual cycle, but it is typically not a calendar year.  So for the line items on the opportunity, the revenue may span 2 calendar years. 

I need to capture how much revenue will fall into each calendar year, at both the Opportunity level and the Account level.

I need to build roll up summary fields on the opportunity object for each year. Then also build roll up summary fields on the account record rolling up that year's revenue from each of its opportunities.  So for example, I would have a 2021 and 2022 revenue field on every opportunity and account.

Solution

You can create a multi-step Rollup Configuration in order to accomplish this use case

First step will be to create the Target fields for these Rollups, we recommend doing this from the Object Manager page of Setup in Salesforce.

• For the first set of Rollups create 2 fields on the Opportunity Product object: 
  
  • Name one for the current year, "2021 Revenue" (or relevant year), and the second for next year "2022 Revenue."
    
• These fields will then be Rolled up to the Opportunity object so we will want to create 2 matching fields on there: 
  
  • Name one for the current year, "2021 Revenue" (or relevant year), and the second for next year "2022 Revenue."
• (Optional) If you would like to take this 1 summary level higher, we can go all the way up to Account. To do this will want to create 2 matching fields on there: 
  
  • Name one for the current year, "2021 Revenue" (or relevant year), and the second for next year "2022 Revenue."

Now that all of the Target fields have been created, creating the Rollup Helper settings can begin

• For the first setting from OpportunityLineItem Schedule to Opportunity Product:
  
  • Select Opportunity Product as the Target Object
    
  • Select 2021 Revenue as the Target field
    
  • Select Line Item Schedule as the Source Object
    
  • Select Line Item ID as the Relationship Field
    
  • Select Sum as the Rollup Type
    
  • Select Revenue as the Source Field
  • You will then want to create a New Filter to include only Line Item Schedule Record for 2021
    • To do this in Step 3 of the Filter creation put 2 filters on The Schedule Date field
      
    • Schedule Date >= 01/01/2021 And  Schedule Date <= 12/31/2021
  • These steps then can be repeated for the Rollup setting going to the 2022 Revenue field, by selecting that as the target field instead, and creating a new similar filter but only to include dates which occurred in 2022.
• Once this initial Rollup has been created you can then create additional settings to pass these results up your data hierarchy:
  • To pass the results up to the Opportunity level from Opportunity Products you can follow these steps:
    
  • Select Opportunity as the Target Object
    
  • Select 2021 Revenue as the Target field
    
  • Select Opportunity Product as the Source Object
    
  • Select Opportunity ID as the Relationship Field
    
  • Select Sum as the Rollup Type
    
  • Select 2021 Revenue as the Source Field
  • Since this Rollup was already filtered in the Rollup we created before this, we do not have to create an additional filter here. These steps can then be repeated for the 2022 Revenue Rollup to Opportunity as well.
• (Optional) The final level of Rollups would be to take this data that is now on Opportunity and pass it up another level higher to the Account Object, to do this you can follow these steps:
  • Select Account as the Target Object
    
  • Select 2021 Revenue as the Target field
    
  • Select Opportunity as the Source Object
    
  • Select Account ID as the Relationship Field
    
  • Select Sum as the Rollup Type
    
  • Select 2021 Revenue as the Source Field
  • Since this Rollup was already filtered in the Rollup we created before this we do not have to create an additional filter here. These steps can then be repeated for the 2022 Revenue Rollup to Account as well.

Need help?