Distinct Count If

hi there,

I'm trying to get a YOY retention rate. We want to calculate how many of the previous year's donors have given this year. And we only want to do this YOY rather than cumulatively.

I've been going about it by trying to get what I would describe as a "distinct count if" but is probably not the right language! I have a table of donors who have given over 5 years. Each unique ID for the donor appears multiple times if they've given more than one gift. This is alongside a Gift Date linked to my dates table.

I have managed to get a cumulative count of unique donors (who may give multiple times a year or not at all), Total Donors per FY (which starts August 1st) and the number of retained donors (although I am not sure if this is definitely a unique count so I need to check.) I need to produce YOY retention rates. So what I would normally do is pull out all the donors who gave in 2018 and then all the donors who gave in 2019 and whatever percentage of the total donors in 2018 gave in both years would give me my retention rate. I am struggling to calculate this year on year. Any suggestions welcome! Apologies if I'm making no sense.