I would like to see how the average Stage Duration has changed over time (ex. today vs. 30, 60, 90 days ago) for open opportunities. But I am having trouble finding the correct report type. Have tried using Opportunity History and Opportunity Trends report type. Opportunity Trends seems to only report the current Stage Duration and not historical (like it does with some other opportunity fields).
Group Columns: Snapshot comparison date
Group Rows: Stage
Data would show average of stage duration for open opportunities in that stage and on that date. Not sure if there is an out-of-the box solution for this type of report.
#Reports & Dashboards
You are on the right track - what you sketched IS the pattern, and the feature is Reporting Snapshots (Setup, Reporting Snapshots). Stage Duration is a live days-in-current-stage value that Salesforce does not historize, which is why Opportunity Trends only ever shows today's number. Snapshots let you capture it on a schedule and trend it yourself:
1. Source report: a tabular or summary report of open opportunities with Stage and Stage Duration, filtered to open.
2. Target custom object: create one with fields mirroring the columns you want to keep, plus a Snapshot Date field.
3. Reporting Snapshot: map the report columns to the custom object fields and schedule it daily or weekly. Each run writes point-in-time rows stamped with the run date.
4. Trend report: build it on the custom object - Group Columns by Snapshot Date, Group Rows by Stage, summarize Average of Stage Duration. Exactly the matrix you described.
Two caveats: snapshots only capture going forward (no backfill for dates before you start), and each run loads up to 2,000 rows, so keep the source filtered to open opps. Historical Trending is the other option but it is limited to a few fixed fields and about 3 months and does not cover Stage Duration well, so Snapshots are the right tool here.
if this helps, please mark it as the Best Answer so it helps the next person - thanks 🙂
