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

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.