#Nonprofit #Reports & Dashboards I was asked to provide a report that shows donors who had a two year+ lapse in donations. This would be easy if it was simply by year, but it is by a date that is going to be unique to each contact. I'm wondering how I might identify a contact's most recent opportunity close date and compare it to their last opportunity close date with a formula field on Contact or a flow or something like that.
Thanks in advance! :)
Hi @Steven Fischer,
alert: there may be a more elegant way to do this (advanced report formulas??) but here's a method that will work
1) Create a checkbox field on a Donor that you will later on use to filter Donors that have a more than 2 year lapse on their donation history
2) Create a schedule triggered flow to populate this checkbox
- create variables: 1) LatestDate (date) 2) CurrentOppCloseDate (date)
- create the following formulas in the flow: 1)Today: today() 2) LapseDuration (number): calculate the number of month between LatestDate and CurrentOppCloseDate. you can find the formula sample here https://help.salesforce.com/s/articleView?id=sf.formula_examples_dates.htm&type=5
- Flow elements:
- Get Opportunity records for the current Donor where stage Closed; store all records; sort desc
- Assign LatestDate - Today
- Loop through opportunities from get opportunities
- for each assign CurrentOppCloseDate - Close Date from the opportunity
- decision: If LapseDuration >24 then assign the triggered contact with the checkbox true and update the contact; if LapseDuration <=24: assign LatestDate with current opportunity from the loop Close date
Hope this helps.
