• Gianfranco
  • 5 Points
  • Member since 2010

  • Chatter
  • 0
    Best Answers
  • 0
    Likes Received
  • 0
    Likes Given
  • 0
  • 2

We needed a way to show lists of opportunities on a rolling 12 months basis, or for the next 365 days.


Since neither of these functions were available for Opportunity reports, I had to create a custom object, with a formula to be able to get these.


I was able to create a formula for an Opportunity report for a rolling 12-month report, it should also work for a Trailing Twelve Month. 


First, create a custom object with a formula.  I called mine Rolling Date


The formula was:


IF(End_Date__c<=TODAY() +365, 1, 0)



In English, if the End Date of our opportunity is greater than or equal to Today's date plus 365 (days), return 1 if true, and 0 if false.



THEN: when creating the report I just filter by the names of the folks I want to include and Rolling Date equals 1.


The formula won't work for Text, it has to be Number. 


It works great, and I assume that by changing the + 365 to a - 365 it will create a TTM report. 


I can't believe that there's no Next 365 or Last 365 in the drop-down for report duration.

Message Edited by RuthlessBunny on 03-12-2010 07:48 AM
I need a little help.  Can any one provide a custom formula that would give me a "rolling 12 month" annual revenue for and account.  Thank you.