Need Help Building a Salesforce Report on Opportunity Value Changes

Ben A. · 2025-03-31 21:52:09.139279+00

Hi everyone, I need help with building a salesforce report. I'm trying to build a report to show the total changes in Opportunity Value of all opportunities and compare it to 30 days ago (did it go up or down by $). I tried using Historical Trends (Amount, 1 month ago, yesterday, change) but it doesn't count an opportunity that was closed as decrease in value. The value stays the same for the opp even if it was closed within the last 30 days. I tried making a custom field that copies the amount if the opp is open and returns 0 if the opp is closed but I am unable to add this custom field to the Historical Trend Tracking. Also, I am having an issue with grouping on a historical trend report since the "change" column disappears whenever I add grouping and I need to add grouping to be able to add this report to a dashboard. Anyone have any ideas on how to best tackle this?

Need Help Building a Salesforce Report on Opportunity Value Changes | RevGenius

5 comments

· Sorted by Oldest

Joe L.
·
·
Consider using snapshot reporting
Joe L.
·
·
https://help.salesforce.com/s/articleView?id=analytics.data_about_analytic_snap.htm&type=5
Ben A.
·
·
Yes, I am currently using snapshots with Historical Trends. However, it only shows the changes on the "Amount" if it was changed but does not show closed opp as a reduction in the amount since technically, the amount is still the same. I tried using a custom field formula with the logic "If Opp is open, get amount value, if Opp is closed lost, return value of 0" to show the difference in value if the opp was closed. However, looks like I cant use formula fields with Historical Trends Report (snapshot).
Muaaz Q.
·
·
The issue with Historical Trend Reporting is that it doesn’t count closed Opportunities as a decrease in value, so here are a couple of ways to work around it: 1. Try Using a Reporting Snapshot Since Historical Trend Reports have this limitation, Reporting Snapshots can help track Opportunity Amount changes over time. You can:
Create a custom object to store daily or monthly Opportunity Amount snapshots.
Set up a Reporting Snapshot to capture Opportunity Amounts automatically.
Use a formula field to compare the latest snapshot with the one from 30 days ago.
Build a report using this data to see total increases or decreases, including closed Opps.
Here's a reference in case you need help setting it up: Salesforce Reporting Snapshots 2. Use a Row-Level Formula in Your Report If you want to work within a standard report, you can create a row-level formula:
Add a custom formula field on the Opportunity that keeps the Amount for open Opps but sets it to 0 when closed.
In your report, create a row-level formula that calculates the 30-day change dynamically.
More details on row-level formulas here: Salesforce Row-Level Formulas 3. Fix the Grouping Issue in Historical Trend Reports If the “Change” column disappears when adding grouping:
Try using a Matrix Report with Opportunity Amount and a row-level formula for tracking changes.
Consider a Joined Report comparing two different timeframes (now vs. 30 days ago).
Hope this helps! Let me know if you need more details. 🚀
Kris
·
·
Why is this even an important metric to track for you?

Joe L.
·
·
Consider using snapshot reporting
Joe L.
·
·
https://help.salesforce.com/s/articleView?id=analytics.data_about_analytic_snap.htm&type=5
Ben A.
·
·
Yes, I am currently using snapshots with Historical Trends. However, it only shows the changes on the "Amount" if it was changed but does not show closed opp as a reduction in the amount since technically, the amount is still the same. I tried using a custom field formula with the logic "If Opp is open, get amount value, if Opp is closed lost, return value of 0" to show the difference in value if the opp was closed. However, looks like I cant use formula fields with Historical Trends Report (snapshot).
Muaaz Q.
·
·
The issue with Historical Trend Reporting is that it doesn’t count closed Opportunities as a decrease in value, so here are a couple of ways to work around it: 1. Try Using a Reporting Snapshot Since Historical Trend Reports have this limitation, Reporting Snapshots can help track Opportunity Amount changes over time. You can:
Create a custom object to store daily or monthly Opportunity Amount snapshots.
Set up a Reporting Snapshot to capture Opportunity Amounts automatically.
Use a formula field to compare the latest snapshot with the one from 30 days ago.
Build a report using this data to see total increases or decreases, including closed Opps.
Here's a reference in case you need help setting it up: Salesforce Reporting Snapshots 2. Use a Row-Level Formula in Your Report If you want to work within a standard report, you can create a row-level formula:
Add a custom formula field on the Opportunity that keeps the Amount for open Opps but sets it to 0 when closed.
In your report, create a row-level formula that calculates the 30-day change dynamically.
More details on row-level formulas here: Salesforce Row-Level Formulas 3. Fix the Grouping Issue in Historical Trend Reports If the “Change” column disappears when adding grouping:
Try using a Matrix Report with Opportunity Amount and a row-level formula for tracking changes.
Consider a Joined Report comparing two different timeframes (now vs. 30 days ago).
Hope this helps! Let me know if you need more details. 🚀
Kris
·
·
Why is this even an important metric to track for you?