Monthly or YOY Reporting in Salesforce with Rollup Helper & Lookup Helper

Free Salesforce business guides to help admins/developers, project managers, sales managers, and decision makers. Download the free PDF series now: Reinventing Your Business, Reimagining Your Salesforce®.

Problem: I need Monthly reporting to show the amount of Opportunities are open this month. This could also be done for Year-over-Year reports.  

Solution: You can use Rollup Helper and Lookup Helper together. For this example, we will do monthly reporting. 

  • From Setup > Object Manager
    • Create a Reporting Month custom Object
    • Create Lookup relationship Field from Opportunity to Reporting Month, called Reporting Month
    • Create new Formula Field on Opportunity to pull Month and Year from an Opportunity, called Reporting Month Formula
      • Set return type to Text
      • Use "Advanced Formula" 
      • TEXT (MONTH (CloseDate)) + "-" + TEXT (YEAR (CloseDate))
    • Create new Currency Field on the Reporting Month Object, called Open Opps Closing this Month
      • Set number decimal places to 2
  • Go into Lookup Helper to automatically relate Opportunities to Reporting Months
    • Create new Lookup Helper setting
    • Select Child Object as Opportunity
    • Maintain Existing Lookup Relationship, using the Reporting Month field
    • Field on Opportunity you want to match is the Reporting Month Formula field
      • Enable Create New Category Records
    • Save and run setting
  • Use Rollup Helper to aggregate Opportunity information on the Reporting Month
    • Create new Rollup Helper setting
    • Target Object is Reporting Month
    • Target Field is Open Opps Closing this Month
    • Child Object is Opportunities
    • Select Type of rollup as Sum
    • Field on Opportunity should be Amount
    • Create new filter to choose only Opportunities closing that month
      • Filter criteria should be Stage, not equal to, Closed Won
      • Another filter criteria is Stage, not equal to, Closed Lost
      • Save filter
    • Save and run rollup

You can also watch this video walkthrough for more details. 

How to Categorize Customers by Order Date in Salesforce Rollup Helper Use Case Library Number of Open Projects on an Opportunity