Calculated properties

Smart CRM & Sales Hub CRM
, ,
3

Happy to help @kennedyp, @SBencheikh you can use this as a starting point, I used Hub Helper Harry a chatGPT trained on HubSpot and a great tool for solving questions like this.

Steps to Calculate Recognized Revenue for the Current Year:

  1. Define the Inputs (these are your existing properties)

    • Total Contract Value (e.g., €1,000,000)
    • Start Date (e.g., July 1, 2024)
    • End Date (e.g., July 1, 2027)

  2. Calculate the Duration of the Contract:

    • Use the difference between the start and end date to calculate the total length of the contract (in days or months). For your example, the contract spans 3 years, or 36 months.

  3. Divide the Total Contract Value Over the Duration:

    • Divide the total contract value by the total contract duration to get the monthly or yearly amount. In your example:
      • Annual Revenue=1,000,0003=333,333.33 € annually \text{Annual Revenue} = \frac{1,000,000}{3} = 333,333.33 \, \text{€ annually}Annual Revenue=31,000,000​=333,333.33€ annually
      • Monthly Revenue=1,000,00036=27,777.78 € per month \text{Monthly Revenue} = \frac{1,000,000}{36} = 27,777.78 \, \text{€ per month}Monthly Revenue=361,000,000​=27,777.78€ per month

  4. Identify the Portion Recognized for the Current Year:

    • For the year 2024, since the contract starts on July 1st, only half of the annual revenue will be recognized (for 6 months). Therefore, the recognized revenue for 2024 will be:
      • 27,777.78×6=166,666.68 € for 2024 27,777.78 \times 6 = 166,666.68 \, \text{€ for 2024}27,777.78×6=166,666.68€ for 2024

  5. Create the Formula in HubSpot:

    • You can use HubSpot’s calculated property feature to create a formula that performs the following:
      • First, subtract the Start Date from the End Date to get the total duration.
      • Then, divide the Total Contract Value by the duration to get the monthly revenue.
      • Finally, sum the monthly revenue for the current year.

Here is a sample formula you could apply:

(Total Contract Value) / DATEDIFF('End Date', 'Start Date', 'months') * DATEDIFF('31/12/CurrentYear', '01/07/2024', 'months')

  • You might need to adjust the formula based on how HubSpot handles date properties and whether you’re working in months or days for accuracy.
  • You can apply similar logic to calculate revenue for subsequent years based on the contract duration.

I would recommend simplifying your example to create a base formula without all of the variables (like whether it starts at the beginning of the year vs mid-year, etc.) with your defined criteria (days vs months) and then running some tests to validate the formula.

If you are looking for someone to provide you with the exact formula it might be best to pursue paid services from a consultant/freelancer or a partner/agency.