topic Re: Calculating Deal Total This Calendar Year in Tips, Tricks & Best Practices

Calculating Deal Total This Calendar Year

ABlasky — Tue, 06 May 2025 21:20:23 GMT

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?

Re: Calculating Deal Total This Calendar Year

JorgeFuentesZ — Wed, 07 May 2025 01:32:21 GMT

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.

Re: Calculating Deal Total This Calendar Year

karstenkoehler — Wed, 07 May 2025 04:19:25 GMT

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!

Re: Calculating Deal Total This Calendar Year

ABlasky — Fri, 09 May 2025 19:23:43 GMT

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