• 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.