Calculate Year-over-Year (YoY) or Quarter-over-Quarter (QoQ) in reports

These instructions use the Opportunities Object as an example, but the principles apply to all Salesforce reporting.

Lightning

1. From 'Reports,' click New Report.
2. Select the 'Opportunities' report type, then click Start Report.
3. Add Opportunity Owner to Group Rows.
4. Add Close Date to the Group Columns.
5. Click the drop-down at the Close Date field in the Preview window, then select Group Date By | Fiscal Year.
    
   
6. Create a Custom Summary Formula field to calculate the value desired:
   1. Select Add Summary Formula from the dropdown in the Columns section.
   2. Give your new column a name and select 'Formula Output Type' as Percent.
   3. Enter one of the following formulas accordingly:
      1. Year over Year:
         AMOUNT:SUM/PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE)
         (Note: the same formula will be used for quarter over quarter progressive)
      2. Q1 (This Year) over Q1 (Prior Year):
         AMOUNT:SUM/PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,4)
         
      3. Year on Year Change %:
         ( AMOUNT:SUM - PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE)) / PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE)
         
      4. Quarter-over-Quarter ( QoQ ) Change %:
         ( AMOUNT:SUM - PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE)) / PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,4)
   4. In the Display Tab, choose Selected Groups then Row Group as Grand Total Only | Column Group as Close Date.
       
      
   5. Click Apply.
7. Click Save & Run.
8. Enter your desired report name and Folder.
9. Click Save and Run Report.

Note: #Error! is returned anytime a formula is dividing by zero. If your formula errors in this way, the denominator in the fraction has returned "0."

Classic

1. From 'Reports,' select New Report.
2. Select the 'Opportunities' report type, then click Create.
3. Select Report Preview in Matrix Format.
4. Drag Opportunity Owner into Row Grouping
5. Drag the Close Date field into the 'Column Grouping' | Hover over field Grouping | Click 'v' | select Group Dates By | Fiscal Year.
6. Create a Custom Summary Formula field to calculate the value:
   1. Select Add Formula under Formulas from the Fields section.
   2. Enter a Column Name and select Format as Percent.
   3. Ensure formula calculation occurs At a specific row/column grouping level...
   4. Enter the following formulas accordingly:
      1. Year over Year:
         AMOUNT:SUM/PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE)
         (Note: the same formula will be used for quarter over quarter progressive)
         
      2. Q1 (This Year) over Q1 (Prior Year):
         AMOUNT:SUM/PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,4)
         
      3. Year on Year Change %:
         ( AMOUNT:SUM - PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE)) / PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE)
         
      4. Quarter-over-Quarter ( QoQ ) Change %:
         ( AMOUNT:SUM - PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE)) / PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,4)
7. Click Save.
8. Enter your desired report name and saving location.
9. Click Save and Run Report.

Note: #Error! is returned anytime a formula is dividing by zero. If your formula errors in this way, the denominator in the fraction has returned "0."