• RuthlessBunny
  • NEWBIE
  • 0 Points
  • Member since 2010

  • Chatter
    Feed
  • 0
    Best Answers
  • 1
    Likes Received
  • 0
    Likes Given
  • 1
    Questions
  • 1
    Replies

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

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.