Calculating volunteer tenure over time across programs (so not quite the same as staff tenure)

[patrickhd]

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

[patrickhd]

Thanks Daxer, really appreciate you taking the time to work at this.
The V Active is perfect with the filter doing what is needed  (and of course, yes, you were right in picking up the <=, thanks).
The V Tenure does not seem to be quite there yet and perhaps I did not explain sufficiently:
- My ultimate goal is to be able to show the average tenure (in bands) of my Active Volunteers over time.

eg. For each period, active volunteer count broken up into those with tenure < 1year, 1-2 years and > 2 years

- How do I "filter" the tenure to only apply to valid volunteers at that time
- Your logic filters seems to only consider the earliest start_date but does not cater for the possibility that the end_date has already occurred eg. If I start on Jan 1,2020 and end on Feb 1, 2020,  but then do the tenure calculation on February the 5th when the person has already left.

Apologies, if this is insufficient for you to go on. I will get back to you with more structured information this weekend but did want to acknowledge and thank you for your input so far. It is invaluable.

[Anonymous]

OK. I got you this:

Be aware that this stuff is fully responsive to any slicers. For instance, in the pic above the LS program has been selected and therefore everything you see in there is relative to this selection.

<-- The Program Stats table

<-- The Programs table

The Time table:

Time = 
SELECTCOLUMNS(
    CALENDARAUTO(),
    "Date", [Date],
    "Year-Month", format( [Date], "yyyy-MMM"),
    "Year-Month Order", int(format( [Date], "yyyyMM" )),
    "Year-Quarter", format( [Date], "YYYY-\QQ"),
    "Time Slice", 
        SWITCH( TRUE(),
            [Date] <= date(2020, 9, 1), "Past + Today",
            "Future"
        )
)

And the measures:

# V Active = 
var LastVisibleDate = MAX( 'Time'[Date] )
var Result = 
    CALCULATE(
        DISTINCTCOUNT( 'Program Stats'[VolunteerID] ),
        KEEPFILTERS(
            'Program Stats'[StartDate] < LastVisibleDate
        ),
        KEEPFILTERS(
            or(
                'Program Stats'[EndDate] > LastVisibleDate,
                ISBLANK( 'Program Stats'[EndDate] )
            )
        ),
        KEEPFILTERS(
            Programs[Program Type] = "Included"
        )
    )
return
    Result


V Tenure = 
// For the current set of volunteers
// and current set of programs, find
// the earliest start date of any
// valid program for this set of volunteers 
// and return the difference
// between the current latest visible date and
// the found date. If no such date exists,
// return BLANK.
var LatestVisibleDate = MAX( 'Time'[Date] )
var EarliestDate = 
    CALCULATE(
        MIN( 'Program Stats'[StartDate] ),
        KEEPFILTERS(
            Programs[Program Type] = "Included"
        ),
        KEEPFILTERS( 
            'Program Stats'[StartDate] <= LatestVisibleDate
        )
    )
var Result = 
    If( NOT ISBLANK( EarliestDate ), 
        INT( LatestVisibleDate - EarliestDate ) )
return
    Result

[Anonymous]

@patrickhd 

Have you already managed to build a solution? If not, I can give you one.

Question 1: Why startdate and enddate have a time component which is always 16:00? If I create a model and a solution, then I'm going to remove things like that which are completely unnecessary and only obscure the picture.

Question 2: Are you sure that this is a correct relation [startdate] < [Date] AND  ( ISBLANK(enddate) or [Date] < [enddate] )? Why not [startdate] <= [Date] AND ( ISBLANK(enddate) or [Date] <= [enddate] )? Why are you excluding the first and last day?