Calculating Quarterly revenue

Smart CRM & Sales Hub CRM
1

Hello,

I’m trying to figure out a way to calculate quarterly revenue in HubSpot. We have the deal amount, we have the deal start and end date, as well as duration in months. We also have fields that have the end of Q1-Q4f or 2024-2027 dates as a constant. I’m trying to figure out how to make a calculated that field that will take the deal amount and give us how much this will generate over the quarters. Some deals could last 1 month and other deals could last up to 18 months so we want to be able to see how much a revenue a deal could bring in by quarter. We also have days in the project field

2

Hey there @CClark3 would this work?

The calculated properties I’d create:

  • A property for monthly revenue: Monthly Revenue = Deal Amount/Duration in Months
  • ​Properties for each quarter’s revenue. For Q1 2024 (example): Q1 2024 Revenue = Monthly Revenue × MIN(DATEDIFF(Q1 2024 End Date,Deal Start Date,“M”),3)
    • You’d repeat this for each quarter you’re tracking.
  • If you want more precise calculations using days, modify the formula to adjust for partial months: Daily Revenue = Deal Amount/Days in Project
    • Then for each quarter:Q1 2024 Revenue=Daily Revenue×DATEDIFF(MIN(Q1 2024 End Date,Deal End Date),MAX(Deal Start Date,Q1 2024 Start Date),“D”)

I’d make sure to test these properties with sample deals to make sure they work, then it’s about the reporting. Create custom reports to visualize:

  • Revenue forecast by quarter
  • Deal performance across quarters
  • Comparison of quarterly revenue targets vs. actuals

This approach should allow you to break down revenue for deals of varying lengths across multiple quarters, providing a clear view of expected revenue over time. Hope that helps!

3

I tried this formula but I’m getting the above error in HubSpot

Error(1).PNG
Error(1).PNG1549×743 58 KB

4

Hmmm… I haven’t built this specific calculated field, it’s just a formula I found. I’d have to tag in someone else who has built more complex formulas like this. @Jnix284 maybe?

5

Thanks for the tag @danmoyle, I haven’t built a recurring revenue model that is broken out quarterly @CClark3, maybe @Shadab_Khan can point you in the right direction or share additional resources.