Snaptshot Roll-Ups At a Specific Date

[kschnaenberg]

Have a base that looks at deal flow, including anticipated close date and value of each deal.  While we can easily view our "pipeline" at any time, struggling to figure out a way to store that snapshot of data on a routine basis.  We don't store updates to pipeline as a record itself, but update the record to the anticipated value.  

At the end of a specific month in the past, what did our pipeline look like in terms of deal value, grouped by when we expect it to close?  Used to look at accuracy (anticipated vs actual) of our project pipeline projections.   

[TheTimeSavingCo]

Do you think you could create an automation that would run every month that would compile all the metrics you want and then create it as a new record in a "Snapshots" table or something?

[kschnaenberg]

Thanks! 

Yes, that is where I was planning on going with it.  Creating the roll-up formulas is where I am struggling as there are lots of contingencies.    

[TheTimeSavingCo]

Ahh roger that.  Yeah...I can't think of a way around that as we'd need to have them set up to see the current metrics as well

Anything in particular you're getting stuck on?

[kschnaenberg]

Each PROJECT has projected conversion date and total value (currency) fields. 

For my SNAPSHOT record I can begin by having a crated field being the last day of the month ending.  Where I am hitting a road block is how to create multiple records then not just one.  I would need to create a the following records:

- Date of month ending
- Projected conversion date
- Roll-up sum of total value on that date

[TheTimeSavingCo · Accepted solution]

Hmm, what if you had an automation that would:
1. Run once a month
2. Find all the "Project" records
3. For each of the found project records, create a record in a "Snapshot Data" table with:
  - Date of month ending (i.e. the run time of the automation)
  - The project's projected conversion date
  - The project's total value

With this information, you could have a "Summary" table that's linked to the "Snapshot Data" table, and each record in the "Summary" table would be one month.  You could then rollup the Snapshot Data records per month and view it there, does that make sense?

[kschnaenberg]

Thank you!  I was overcomplicating it.  This worked.  

[TheTimeSavingCo]

Glad I could help!