Custom Formula for Open Deals x Time

[JZwagemaker]

Hi! I need help figuring out how to create a formula to create a custom field where we can calculate the total number of open deals for a specific account in a specific time period. For example, the number of open deals Company X has in 2026. Thank you!

[karstenkoehler · Accepted solution]

Hi @JZwagemaker,

A formula is not the right tool here, I would recommend the historical snapshots report in the sales analytics: "This report shows the number of deals at certain points in time by category (e.g., deal stage, amount), depending on the tab you select."

https://knowledge.hubspot.com/reports/create-sales-reports-in-the-sales-analytics-suite#deals

Best regards

[karstenkoehler · Accepted solution]

@JZwagemaker you can visualize how much was closed or created in a given year, yes – but it's not possible with HubSpot's native reporting tools to visualize how many open deals there were outside of the historical snapshots report. You would have to export raw data from HubSpot and visualize it outside of HubSpot or leverage third-party tools / integrations to accomplish this.

One option could be creating a multiple checkboxes property with options for each year, then using a workflow to enroll deals that were open in that year to append the corresponding value. I'm not sure if the report will display correctly but that could be an option. It does however require some fine-tuned enrollment criteria (create before or in that year, closed in or after that year).

[JZwagemaker]

I was able to extract the data I needed by creating this custom report

[karstenkoehler · Accepted solution]

@JZwagemaker as far as I can tell, this will return the current number of open deals, not the number of deals that were open in a given year.

[JZwagemaker]

Hi Karsten,

Thanks for your response. I reviewed the sales analytics suite but I haven't been able to find or create a report that shows deal data on a per account basis. I would like to see a table as follows:

	2025 Deals	2026 Deals	2027 Deals	2028 Deals
Account A	$1,000,000	$2,000,000	$3,000,000	$4,000,000
Account B	$600,000	$1,000,000	$700,000	$900,000
Account C	$1,000,000	$1,000,000	$1,000,000	$1,000,000

Hopefully you can point me in the right direction on how to create such an overview. I haven't been able to make it happen with a customer report using Accounts and Deals as data source, which is why I considered a formula field.

Thanks!

Jacob

[karstenkoehler · Accepted solution]

@JZwagemaker you can visualize how much was closed or created in a given year, yes – but it's not possible with HubSpot's native reporting tools to visualize how many open deals there were outside of the historical snapshots report. You would have to export raw data from HubSpot and visualize it outside of HubSpot or leverage third-party tools / integrations to accomplish this.

One option could be creating a multiple checkboxes property with options for each year, then using a workflow to enroll deals that were open in that year to append the corresponding value. I'm not sure if the report will display correctly but that could be an option. It does however require some fine-tuned enrollment criteria (create before or in that year, closed in or after that year).

[karstenkoehler · Accepted solution]

Hi @JZwagemaker,

A formula is not the right tool here, I would recommend the historical snapshots report in the sales analytics: "This report shows the number of deals at certain points in time by category (e.g., deal stage, amount), depending on the tab you select."

https://knowledge.hubspot.com/reports/create-sales-reports-in-the-sales-analytics-suite#deals

Best regards