'Days Since' Chart with a unique issue

[John_Pikowski]

Hello there,

Thanks to this community I’m well on my way to creating a “days since last contacted” graph, but I’m running into an issue.

I have a rollup column that uses the MAX() function, and then a formula column that runs this calculation:

DATETIME_DIFF(TODAY(),{Last Spoke To},‘days’)

There wouldn’t be an issue, but the MAX() function is pulling from a long list of events, some which have not happened yet . This is because I have a Zapier integration to pull in all my calendar events as they are scheduled. This causes certain “Days since last contacted” values to be negative, because the max value is in the future.

So my ultimate question is how to leverage the TODAY() function so MAX() can ignore any events that will occur after today. Hope this makes sense - thanks!

[John_Pikowski]

Managed to come up with a solution, so just thought I’d share:

1. Created a secondary Date Column [Raw Date]

Blockquote IF({Date} < TODAY(), DATETIME_FORMAT({Date}, ‘MM-DD-YYYY’), BLANK())

This ensures that if the date has not happened yet, the value will not populate.

2. Create one additional column “Days Since Meeting”

Blockquote IF({Raw Date} != “”, DATETIME_DIFF(TODAY(),{Date}, ‘Days’),BLANK())

This calculates the days since the meeting has occurred, and DOES NOT pull negative values.

3. Rollup the “Days Since Meeting” with a

Blockquote MIN(Values)

call, and I’m all set now.