CRM

miavelasco
Contributor

Filter a calculated property based on date field

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?

9 Replies 9
CSaute
Participant | Elite Partner
Participant | Elite Partner

Filter a calculated property based on date field

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 !

IZabarsky
Member

Filter a calculated property based on date field

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!

0 Upvotes
CSaute
Participant | Elite Partner
Participant | Elite Partner

Filter a calculated property based on date field

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 !

0 Upvotes
HelloAaron
Member

Filter a calculated property based on date field

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

0 Upvotes
HelloAaron
Member

Filter a calculated property based on date field

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

EmmaAtKiwi
Top Contributor | Platinum Partner
Top Contributor | Platinum Partner

Filter a calculated property based on date field

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


Emma Washington

HubSpot Director @ Kiwi Creative


3x Accredited HubSpot Platinum Partner

HubSpot Certified Trainer
Leader of B2B Technology (USA) HubSpot User Group


Join our HubSpot Community Group for B2B Technology
miavelasco
Contributor

Filter a calculated property based on date field

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

0 Upvotes
EmmaAtKiwi
Top Contributor | Platinum Partner
Top Contributor | Platinum Partner

Filter a calculated property based on date field

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.


Emma Washington

HubSpot Director @ Kiwi Creative


3x Accredited HubSpot Platinum Partner

HubSpot Certified Trainer
Leader of B2B Technology (USA) HubSpot User Group


Join our HubSpot Community Group for B2B Technology
0 Upvotes
JDrejer
Participant

Filter a calculated property based on date field

Hi @emma

Sorry to inturupt! 🙂 - 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.

0 Upvotes