+ Start a Discussion
Johan Emerén 2Johan Emerén 2 

Field calculations

Hi,

I'm having trouble calculating the value of an opportunity based on the end of the year.

So if OP X has an estimated live date 02/02/2018 I'd like to see the amount that would generate until the end of the year.

Months left is easy "13 - MONTH(TODAY())"

And the value should be something like this but it doesnt seem to work "Opportunity_Value__c *(MONTH(Estimated_Live_Date__c) + (12*(YEAR(Estimated_Live_Date__c)-YEAR(TODAY()))) - MONTH(TODAY()))"

Can someone help me please?

Best Answer chosen by Johan Emerén 2
Jainam ContractorJainam Contractor
Hi Johan,

Amount * (( DATE(YEAR(CloseDate),12,31) - CloseDate ) / 30) formula works fine for me.

Also it will do what you want i.e Value * (amount of months between estimated live date until the end of the year) as we have divided the no of days by 30 so indirectly we are converting it to months from days.

So replacing the CloseDate with another date field and the Amount with another number/ currency field should not be a problem.

Please find the below snap for the same:

User-added image

Please let me know if it helps.

Thanks,
Jainam Contractor

All Answers

Jainam ContractorJainam Contractor
Hi Johan,

Please find the below formula that i used to Calculate the Opportuntiy Value based on the CloseDate and Amount.

Custom Field: Opportunity Value
API Name: Opportunity_Value__c

Opportunity_Value__c = 
Amount * ( 

MONTH(CloseDate) + 12*(YEAR(CloseDate) - YEAR(TODAY())) 

- MONTH(TODAY()) + 1
)

Eg: If Amount = $10,000, CloseDate = 06/30/2018, Today's Date = 1/24/2018 then the Value for Opportunity_Value__c = $60,000

Please find the below snap for the Same:

User-added image
Please let me know if it solves your problem and if you need any more assistance. Please mark this answer as the Solution if it solves your problem.


Thanks,
Jainam Contractor,
Salesforce Consultant,
Varasi LLC
www.varasi.com
Johan Emerén 2Johan Emerén 2

Hi Jainam,

Thank you for the thourough explanation and assistance.

I may have explained myself incorrectly, the value should be the amount this opportunity would generate from it's estimated live date until the end of the year. This seems to calculate the value from now until the ELD.

But maybe I did something wrong.

Also, would it be possible to take the days into account? Since the estimated live date here is the 31/10 that month should preferably not count as a full month.

User-added image

Jainam ContractorJainam Contractor
Hi Johan,

Yes we can consider days as well. Please look at the below formula:

Amount * ( DATE(YEAR(CloseDate),12,31) - CloseDate ), this considers CloseDate and Amount.

For your case, you can use something like this:

Opportunity_Value__c ​* (( DATE(YEAR(Estimated_Live_Date__c),12,31) - Estimated_Live_Date__c) / 30).

This will intially calculates the number of days left in that Year and then convert into Approximate Month by dividing by 30.

Please let me know if it solves your problem and mark it as the best answer if it does. Please let me know if any concerns.

Thanks,
Jainam Contractor.
 
Johan Emerén 2Johan Emerén 2

Amount ​* (( DATE(YEAR(Estimated_Live_Date__c),12,31) - Estimated_Live_Date__c) / 30)

This gives me a syntax error, my inexperienced guess would be that it attempts to take the amount times a date value.

Aside from that it looks like it should do what I want

Value * (amount of months between estimated live date until the end of the year)

Jainam ContractorJainam Contractor
Hi Johan,

Amount * (( DATE(YEAR(CloseDate),12,31) - CloseDate ) / 30) formula works fine for me.

Also it will do what you want i.e Value * (amount of months between estimated live date until the end of the year) as we have divided the no of days by 30 so indirectly we are converting it to months from days.

So replacing the CloseDate with another date field and the Amount with another number/ currency field should not be a problem.

Please find the below snap for the same:

User-added image

Please let me know if it helps.

Thanks,
Jainam Contractor
This was selected as the best answer
Johan Emerén 2Johan Emerén 2

Thank you so much for your help, this works perfectly.

Now if I wanted to go a step further and have this take into account ramp up, would that be possible?

The first six months would generate the following of the "Amount" field.


10%
20%
40%
60%
80%
100%