Measure to find date range an event with date

[tagban]

I have a database with IDs for each unique person, that's linked to various times they were enrolled/disenrolled from our program, and a 3rd set of data that contains events with a single date of when it happened. I'm trying to display the Enrollment/Disenrollment date range that an event happened specifically I have two seperate measures one for enrollment, one for disenrollment but what I'm struggling with is I'm used to looking at MIN/MAX and in reality someone can have multiple enrollments/disenrollments as to when these events fell. Because the logic surrounding this is crucial I wanted to ask here.

The current measure I wrote:

EnrollEnd = 
VAR enrollStart = MIN('Program Modifier Enrollments'[program_modifier_enrollment_start])
VAR enrollEnd = MAX('Program Modifier Enrollments'[program_modifier_enrollment_end_adjusted])
VAR enrollEndWithNull = MAX('Program Modifier Enrollments'[program_modifier_enrollment_end])
VAR assessmentDate = MAX('Events'[assessment_end_date_utc])
VAR StartDate =
    SWITCH(
        TRUE(),
        assessmentDate <= enrollEnd && assessmentDate >= enrollStart,
        1,0
    )
    Return enrollEndWithNull

The above what I WANT it to do, is pick when the assessment happened and display the date of the enrollment end (as a seperate column). Because this is in a data flow, I have to use measures to build this out. 

The idea here for me, is that I'd have two measures one for Start and one for end dates, that way it displays. But because each person can have multiple start/end dates, it is not correctly assigning the date as expected above. 

Each row in this contains the person's unique ID, and the event date/id, so normally in my head I'd have merged the queries based on the data, but I think the measure is a cleaner option (Or rather the Column). Any support would be greatly appreciated. 

Updated:
Events Table:

person_id	event_date	SpellExpected
7218	5/14/2016	1
6219	2/3/2015	1
6219	3/4/2020	2
5542	6/17/2021	1
5542	11/12/2021	1
7218	6/12/2017	1
7218	7/1/2022	2
6219	6/1/2023	3
5542	4/1/2024	1

Enrollment Table:

person_id	enroll_start	enroll_end	EnrollmentSpell	Status
6219	1/1/2015	2/1/2020	1	Disenrolled
7218	2/3/2016	3/15/2019	1	Disenrolled
7218	3/4/2022		2	Enrolled
6219	3/1/2020	4/1/2020	2	Disenrolled
6219	4/2/2021	6/2/2023	3	Disenrolled
5542	6/15/2021		1	Enrolled

Note: Spells are there only to validate results, and aren't actually part of the data source. 

Expected results:

person_id	event_date	SpellExpected	Enroll Start	Enroll End	Spell
7218	5/14/2016	1	2/3/2016	3/15/2019	1
6219	2/3/2015	1	1/1/2015	2/1/2020	1
6219	3/4/2020	2	3/1/2020	4/1/2020	2
5542	6/17/2021	1	6/15/2021		1
5542	11/12/2021	1	6/15/2021		1
7218	6/12/2017	1	2/3/2016	3/15/2019	1
7218	7/1/2022	2	3/4/2022		2
6219	6/1/2023	3	4/2/2021	6/2/2023	3
5542	4/1/2024	1	6/15/2021		1

[tagban · Accepted solution]

I did resolve this with a Calculated Column:

a.Assessments_EnrollDateRange = 
VAR CurrentEventDate = 'Assessments'[assessment_start_date]
VAR CurrentMemberID = 'Assessments'[people_id]
VAR Result =
    CALCULATE (
        VAR StartDate = SUM('Enrollments'[enrollment_start].[Date])
        VAR EndDate = SUM('Enrollments'[enrollment_end_adjusted].[Date])
        RETURN
        IF (
            NOT ISBLANK(StartDate) && NOT ISBLANK(EndDate),
            CONCATENATE(StartDate, CONCATENATE(" - ", EndDate) ),
            BLANK()
        ),
        FILTER (
            ALL ( 'Enrollments' ),
            'Enrollments'[people_id] = CurrentMemberID &&
            CurrentEventDate >= 'Enrollments'[enrollment_start] &&
            CurrentEventDate <= 'Enrollments'[enrollment_end_adjusted]
        )
    )
VAR MostRecentEnrollment =
    CALCULATE (
        CONCATENATE(
            MAX('Enrollments'[enrollment_start].[Date]),
            CONCATENATE(" - ", MAX('Enrollments'[enrollment_end_adjusted].[Date])
        )),
        FILTER (
            ALL ( 'Enrollments' ),
            'Enrollments'[people_id] = CurrentMemberID &&
            'Enrollments'[enrollment_start] <= CurrentEventDate
        )
    )
RETURN
IF ( ISBLANK ( Result ), MostRecentEnrollment, Result )

[v-yaningy-msft]

Hi, @tagban 

If a person registers multiple times, is his ID the same? You can work with non-sensitive data screenshots to aid in understanding your needs or share pbix files, all text is really not easy to understand.

Best Regards,
Yang
Community Support Team

[tagban]

I've done a lot of logic checks, and testing, I came up with what I thought made sense but its just showing the Max for the entire list of enrollment dates and not individualizing each person's enrollment date aligned with when the event happened. Here's my measure (Slightly altered to be less confusing since our table naming conventions leave a lot to be desired)
I feel like I'm missing a step to properly align the people_id from Enrollments with the assessment so I only get the various enrollment spans that that particular person has.

EnrollRange_ASSESSMENT = 
  VAR __date = MAX('ASSESSMENT'[assessment_start_date])
  VAR __ID = MAX('ASSESSMENT'[people_id])
  VAR __EnrollRange = 
    MAXX(
      FILTER(
        'ENROLLMENTS',
        'ENROLLMENTS'[people_id] = __ID &&
          __date >= 'ENROLLMENTS'[enrollment_start] &&
            __date <= 'ENROLLMENTS'[enrollment_end]
      ),
    CONCATENATE(
	'ENROLLMENTS'[enrollment_start].[Date], 
		CONCATENATE(" - ",'ENROLLMENTS'[enrollment_end_adjusted].[Date]))
    )
RETURN
  __EnrollRange

Worth noting that the end_adjusted is always a future date when they're currently enrolled. (And yes I know the one above I forgot to leave it there, so jsut assume its there, I can't seem to edit it?

[tagban]

I do understand the request for pbix/pictures. What I can do is maybe make a table of data to play with. The data I'm using is mostly very sensitive and cannot be exposed publicly unfortunately. I'll respond with an updated data set to work with shortly. Thanks!