Calculating Deal Total This Calendar Year

[ABlasky]

I am working on writing a custom deal property that calculates the expected annual amount based on the close date. The ultimate goal is to include the custom deal property on our pipeline reporting to show us the total annual amount (an existing HubSpot-generated field based on 12 months) vs the annual amount expected in the current calendar year if the deal is won. 

Has anyone written a custom formula that calculates this?

[JorgeFuentesZ · Accepted solution]

Hey there! Let me know if this would be of help:

Essentially, we'd use a formula that calculates the prorated amount for the remaining months in the current year. The formula could look something like this: (Deal Amount / 12) * (12 - MONTH(Close Date)) Here’s the breakdown:

• Deal Amount is the total value of the deal.
• Divide it by 12 to get the monthly value.
• Subtract the month of the close date from 12 to calculate the remaining months in the year.
• Multiply the monthly value by the remaining months.

As you can see, I did a testing example with a hypothetical deal of 100K closing on March 31s, and it prorated the expected amount to 66K.

Is this more or less what you were looking for? I'd love to be able to help further if not.

[karstenkoehler · Accepted solution]

Hi @ABlasky,

A formula could look like this:

Formula for copying: [properties.amount]*((12-month([properties.closedate])+1)/12)

For a date in May and an annual amount of 12k, this would return 8k, for a date in June 7k, etc.

As an explanation:

• month(Close Date) gives the month number (e.g., May = 5).

• 12 - month(Close Date) + 1 gives the number of months remaining in the year including the close month.

• Dividing by 12 prorates the annual amount.

Best regards!

[JorgeFuentesZ · Accepted solution]

Hey there! Let me know if this would be of help:

Essentially, we'd use a formula that calculates the prorated amount for the remaining months in the current year. The formula could look something like this: (Deal Amount / 12) * (12 - MONTH(Close Date)) Here’s the breakdown:

• Deal Amount is the total value of the deal.
• Divide it by 12 to get the monthly value.
• Subtract the month of the close date from 12 to calculate the remaining months in the year.
• Multiply the monthly value by the remaining months.

As you can see, I did a testing example with a hypothetical deal of 100K closing on March 31s, and it prorated the expected amount to 66K.

Is this more or less what you were looking for? I'd love to be able to help further if not.

[ABlasky]

Thank you! This is exactly what we're looking for.