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
khayum33khayum33 

Calculate Quarterly Revenue Amount based on Start and End Dates

Any help with this solution is greatly appreciated!

 

Advertising company needs to report the Qtrly revenue amount, for ads running. More specifically: $Amount for deals with Prob > 50%, where the ad run date(s) encompasses X Qtr.

 

Here are the fields I have and an example:

  • Amount $12,000.00 [standard field]
  • Start Date 8/15/2011 [custom field created]  
  • End Date 10/20/2011 [custom field created] 
  • Number of run days: 67 [custom field: End_Date__c - Start_Date__c +1]
  • daily rate: 179.10 [custom field: Amount / Number_of_Run_days__c]

Desired results for reporting:


Total Days

Total Amount

Q3

47

$8,417.91

Q4

20

$3,582.09

 

Ref details for this example:

17 days (8/15/2011 -8/31/2011) 17*179.10= 3,044.78

30 days (9/1/2011-9/30/2011) 30*179.10= 5,373.13

20 days (10/1/2011-10/20/2011) 20*179.10= 3,582.09

 

Thank you for your time!

Karen

Shashikant SharmaShashikant Sharma

Create a formula fied for calculate rate if you want to show rate as well create this

 

Amount__c  / ( EndDate__c - StartDate__c + 1 ) )

 

To calculate desired result you can use this rate .

 

For caculation of desired result your final formula (Select Return type as : Currency) would be this use this

 

TotalDays__c * (Amount__c  / ( EndDate__c - StartDate__c + 1 ) )
khayum33khayum33

I appreciate the reply!

 

I have the daily rate and number of days. What I'm missing is how to report the revenue amount when the Start/End period encompasses this Qtr (or next). Some deals will have revenue for every day in a qtr, other could have 10 days in current qtr and 10 in the next...

 

Total Days       Total Amount

Q3       47        $8,417.91

Q4       20        $3,582.09

 

I hope this makes sense.

Thank you!

Karen
Shashikant SharmaShashikant Sharma

To the extent I am getting idea of your issue let me give you this suggestion ,

 

Create a child custom object Deal with a field Revenue and a Master detail for the oject for quarter revenue , 

In the parent object create a roll up field which has the sum of all deals , use this roll up sum of all deals to calculate Quarterly Revenu.

Step 1 : Create new Child Object

Step 2 : Roll up field for sum

Step 3 : Formula field using this sum for calculations
khayum33khayum33

I tried Rollup* Sum a while ago BUT The missing piece is, How do I calculate the number of days within EACH Quarter based on Start_Date__c & End_Date__c.

 

Here's a good example that should clarify what I need:

Start_Date__c 6/29/11, End_Date__c is 10/20/1. I need to know: there are 2 Days Revenue in Q2.  92 Days Revenue for Q3, and 20 Revenue Days in Q4.

 

*[This Quarter] Smart Dates do not work with rollup summary formulas. (To solve this issue I'd gladly update the rule every 90 days!)

 

Thank you!

 

Any thoughts on other areas to post, how to promote this issue?

Be Well,

Karen

 

 
Travis Lee 1Travis Lee 1
Hi Karen,

I'm also struggling with this issue. I know this is a very late response but were you ever able to achieve this goal? I'd be very interested to learn more about it. Please let me know when you have a chance!

-Travis
Suzann Gaito 9Suzann Gaito 9
Anyone out there have a fix for this? I have the same exact need, quarterly revenue reporting based on the Start and End Dates.  I'll keep looking but if anyone has a reference to share that would be great.  Thanks!
Neena Bains 32Neena Bains 32
1. You will need 4 different formula fields Q1 Revenue, Q2 Revenue, Q3 Revenue and Q4 Revenue.
2. And, a number field to store the fiscal year for which the calculation is done (without which the data will change as the days go by). I have added a custom field that stores a 4 digit year (Fiscal_Year__c).

And the formula for Q1 Revenue Days is
 
IF( AND(Start_Date__c <= Date(Fiscal_Year__c, 3, 31), Start_Date__c >= Date(Fiscal_Year__c, 1, 1), End_Date__c <= Date(Fiscal_Year__c, 3, 31), End_Date__c >= Date(Fiscal_Year__c, 1, 1)), End_Date__c - Start_Date__c, IF( AND(Start_Date__c <= Date(Fiscal_Year__c, 3, 31), Start_Date__c >= Date(Fiscal_Year__c, 1, 1), End_Date__c > Date(Fiscal_Year__c, 3, 31)), Date(Fiscal_Year__c, 3, 31) - Start_Date__c, IF( AND(Start_Date__c < Date(Fiscal_Year__c, 1, 1), End_Date__c <= Date(Fiscal_Year__c, 3, 31), End_Date__c >= Date(Fiscal_Year__c, 1, 1)), End_Date__c - Date(Fiscal_Year__c, 1, 1), IF( AND(Start_Date__c < Date(Fiscal_Year__c, 1, 1), End_Date__c > Date(Fiscal_Year__c, 3, 31)), Date(Fiscal_Year__c, 3, 31) - Date(Fiscal_Year__c, 1, 1), 0))))
You just have to replace the quarter start date and quarter end date to make it work for other quarters.

For example,
replace Date(Fiscal_Year__c, 1, 1) with Date(Fiscal_Year__c, 4, 1) and Date(Fiscal_Year__c, 3, 31) with Date(Fiscal_Year__c, 6, 30) for Q2 and so on.