Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
khayum33

# 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

Karen

Shashikant 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 ) )

khayum33

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

khayum33

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