function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
RuthlessBunnyRuthlessBunny 

Solved! Rolling 12 Months or Next 365 Days

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
GianfrancoGianfranco

I think you can get a TTM Opportunity report without creating any fields.

 

Run a report on all Opportunities and apply a custom filter as follows:

 

Close Date  equals  LAST 365 DAYS

 

Hope this helps.

 



 

 
Sushma RamakrishnanSushma Ramakrishnan
Hi RuthlessBunny,

Thanks so so so much for Sharing & Explaining in detail and providing the solution for Rolling & TTM 12 Months...!!!
It works like charm...!
Sushma RamakrishnanSushma Ramakrishnan
Hi RuthlessBunny,

The formula i have used is as below :
IF( PO_Approval_Date__c <= Vendor__r.Current_CY_As_Of_Date__c -365 , 1, 0)

PO_Approval_Date__c : is a Date field -> Date of Spend.
Vendor__r.Current_CY_As_Of_Date__c : is a Date field -> Max of Date as per the data uploaded in Spend object.

What is basically want to achieve is : For eg: If I have loaded data in my Spend object till Nov2016.Then in 12 months rollling report itn should show me data from Nov 2016 to Dec2015.
Currently by using the above formula its showing me data till Nov2015.
Please help me correct this.

Thanks for All & Any Help in Advance...!!!