My executives would like to have a dashboard that shows pipeline trends by month - new opportunities added to the pipeline + existing opportunities = total opportunities - closed won opportunities - closed lost opportunities = ending pipeline.

My idea was to create a custom field that would return the a date equal to the first day of the month when the opportunity was created.  Done, easy.

Then I wanted to write case where As Of Date = Opp Created first date of month, "new", "existing".  Then I could use the closed/won status to do the rest of the calculations.

Except the As Of Date isn't a field I can see to reference.  Am I starting in the wrong place? Is there a better way to accomplish this report? Is the only way to do this is with an Analytics Snapshot?