Filter a calculated property based on date field

CRM & Sales CRM
1

I’d like to create a custom calculated property that sums the total revenue fiscal year to date. However, it doesn’t seem possible to filter the calculation by date.

How can I achieve this in HubSpot?

2

Hi @miavelasco! To create a property that calculates the sum of the total revenue to date:

  1. create a new Company property (to see total revenue by company)
  2. under field type, select Calculation
  3. click Build Calculation
  4. under Calculated Property Type, select Sum
  5. under Choose the Associted Record Type, select Deal
  6. under Choose the Associated Record Property, select Amount
  7. click Create

You can now include your Total Revenue field in reports, lists, and/or company filters to see companies that have spent over a certain amount, best performing industries, etc.

Screen Shot 2023-03-22 at 2.48.04 PM.png
Screen Shot 2023-03-22 at 2.48.04 PM.png1940×900 107 KB

3

thanks @EmmaAtKiwi . specifically what I’m looking for is the ability to add a condition to the calculated property for deals received within the fiscal year. It doesn’t appear possible to add a date field.

Screenshot 2023-03-22 at 1.33.35 PM.png
Screenshot 2023-03-22 at 1.33.35 PM.png1710×816 96.8 KB

4

Hmmm, perhaps a custom report that displays the Total Revenue by the Deal Close Date, and you can include a filter where the Close Date is less than 360 days ago, for example.

5

Hi @emma
Sorry to inturupt! :slightly_smiling_face: - I think also what the author meant (and what I can see benefit in too) is on company level (in relation to deals associated) see how much revenue was generated in a fiscal year, and not just across time (like the default Total Revenue) - hence why we want to sort/filter on data range.

6

Hi there,
I have a solution for this issue.

First, I created a deal property called “Deal Year”, which I calculate by workflow. If you have Operations Hub Pro or Enterprise, use data formatting to do this. If you don’t have Operations Hub, then a workflow based on closing date with branches will do.
EDIT : you can use calculated property to do so as well : define it as a “number” type and use the custom equation with the function “year(close date)”. It will do the job, even without Operation Hub !

Next, I created a calculated contact property, of type sum. I selected the deal object, then the property Amount to be summed, and in the filtering conditions, I took my “Deal Year” property == 2023.

It’s obviously imperfect because you have to create a calculated property for each year, or remember to change this value every year, but it works until Hubspot allows date-based filtering.

Hope it helps !

7

The Deal Year property doesn’t show up as a filter option when I’m creating the calculated property. Any idea why?

8

I figured out what was wrong: the Deal Year property needs to be setup as a Number.

9

Hi @CSaute
Can you share how you built the “workflow based on closing date with branches will do”
to achieve only the year of close date field into the “Year date” property?
Thanks!

10

You just have to use a “if/then” branch (with filters conditions), and your triggers will be like “If close date is between 01/01/2024 and 31/12/2024” then “Deal year”=2024, etc.

EDIT : I just realised you can use calculated property to do so as well : define it as a “number” type and use the custom equation with the function “year(close date)”. It will do the job, even without Operation Hub !