Calculating volunteer tenure over time across programs (so not quite the same as staff tenure)
Hi, I am helping out a not for profit with some volunteer reporting off their CRM. I am looking to solve a slight variation on the typical employee tenure question for which many posts already provide solutions. The intent is to show how their retention of volunteers has changed over the years by showing volunteer tenure over time. The simplified data model is as follows: Table 1 : DIM_CALENDAR : Dates table Table 2: FACT_volunteer_details : volunteerID, Name Table 3: FACT_volunteering_dates: startdate, enddate, programID, volunteerID [so dates per program and volunteer] Note, unlike an employee who is either with a company or not at any given point in time: Volunteers may be involved in more than one program at the same time, so there can be multiple volunteering_dates entries for the same period for the same volunteer Volunteers (as would be for employees) can leave a program and then later start again for that same program I simply want to be able to show at any point in time the tenure of volunteers across bands: eg. < 1 year, 1-3 years, 3+ years (the data will ultimately decide what breakups will be used). So a Stacked Column chart where: - X Axis is the Date hiearchy - Values is Volunteer Count - Legend is Tenure_Band (after that I will also be looking at showing this across various dimensions such as Age etc but my challenge is getting Tenure over time sorted). So I expect I am looking for a Measure for Tenure and a Measure for Active Volunteers. All the staff tenure examples are based on a one-for-one relationship where at any time an employee is either employed or not. I have not managed to alter the logic to handle this variation: Rules: On any particular date: A volunteer is considered active at a particular date if: 1. They have an associated volunteering_dates record falling on that date so: [startdate] < [Date] AND [ISBLANK(enddate) or [Date] < [enddate]) AND 2. the programID = “VT” OR “MT” OR “LS” (but not “BM” (there are more exceptions in reality) So for example on 31/10/2019 for the data I have provided below: B2 (Lisa) , A4(Abdel) and D5(Josephine) are ACTIVE A1 (John) is not as finished before that date C3 (Carlos) is not as was not in a valid program type even though he has a date record on that date For these active volunteers Volunteer Tenure at that point is considered: [Date] – EARLIEST startdate of any valid program (so in example below Josephine's (D5) tenure on 31/10/2019 is 71 days being for the days between her earliest valid program start date of 21/08/2019 16:00 for LS to 31/10/2019, her MT program startdate of 24/10/2019 being later than LS is ignored) Please find simplified sample tables as: Table 1 : DIM_CALENDAR : < please use any standard calendar table to allow the reporting over time - I have created inactive relationships to startdate and enddate respectively to allow measures to use these > Table 2: FACT_volunteer_details : volunteerid fullname A1 John B2 Lisa C3 Carlos A4 Abdel D5 Josephine Table 3: FACT_volunteering_dates: startdate enddate volunteerid volunteering_program 26/02/2019 16:00 17/10/2019 16:00 A1 VT 17/07/2019 16:00 5/02/2020 16:00 B2 VT 16/02/2021 16:00 B2 VT 27/05/2019 16:00 18/01/2021 16:00 C3 BM 9/09/2019 16:00 A4 VT 24/10/2019 16:00 31/12/2020 16:00 D5 MT 21/08/2019 16:00 31/08/2020 16:00 D5 LS . Any help would be much appreciated, still very much learning. Thanks