Find Next Enrollment for Client Based on Certain Conditions

[heatherkw]

Hello, I am still pretty new to DAX and I have looked around for solutions, but they are not quite the same as my scenario, or I'm just not well-versed enough in DAX to understand how to make it work with my data. 

I need to determine if a client returned to our system within 12 months, but several criteria have to be met. Their prior exit must have been to a Permanent Exit Type. Their new enrollment is only counted if it's a certain Program Type as well (Emergency Shelter being one of these). 

Here is some made up data as an example of what I would need it to return in the Returns column on the far right. As you can see, Enrollment ID 1234 has a 1 in the return column because they had a permanent exit for that enrollment and then later enrolled in Emergency Shelter again within 12 months. Enrollment ID 1237 has a 0 though because the subsequent enrollment was to a Diversion program, which is not counted and the later Emergency Shelter enrollment was just over 12 months later. Returns are not calculated at all for Non-Permanent Exit Types. 

EnrollmentID	PersonalID	Enrollment Date	ProgramType	ExitDate	ExitType	Returned12Months
1234	1234	1/1/2018	Emergency Shelter	4/30/2018	Permanent	1
1235	1234	6/24/2018	Emergency Shelter	9/21/2018	Non-Permanent
1236	1234	10/1/2018	Emergency Shelter	10/3/2018	Non-Permanent
1237	1234	12/1/2018	Emergency Shelter	3/31/2018	Permanent	0
1238	1234	5/1/2019	Diversion	10/1/2019	Non-Permanent
1239	1234	12/2/2019	Emergency Shelter

Thank you for your help!

[v-yanjiang-msft]

Hi @heatherkw ,

According to your description, here's my solution.

Create a calculated column.

Column =
IF (
    [ExitType] = "Permanent",
    IF (
        MAXX (
            FILTER (
                'Table',
                'Table'[EnrollmentID]
                    = EARLIER ( 'Table'[EnrollmentID] ) + 1
            ),
            'Table'[ProgramType]
        ) = "Emergency Shelter"
            && DATEDIFF (
                'Table'[ExitDate],
                MAXX (
                    FILTER (
                        'Table',
                        'Table'[EnrollmentID]
                            = EARLIER ( 'Table'[EnrollmentID] ) + 1
                    ),
                    'Table'[Enrollment Date]
                ),
                MONTH
            ) <= 12,
        1,
        0
    )
)

Get the result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[heatherkw]

I'm also finding that in the real data, this doesn't seem to work. The below records are based on real data. With this calculated field, it returns a 1, but it should return a 0. The person exited to a Permanent destination on both 1/4/2021 and 1/9/2021, and they don't have any enrollment entry dates after 1/9/2021 in any program after that, so it should return a 0. 

EnrollmentID	PersonalID	EntryDate	ProgramType	ExitDate	ExitType	Return
1234	8888	5/28/2020	Coordinated Assessment	1/4/2021	Permanent	0
1235	8888	6/10/2020	PH - Rapid Re-Housing	1/9/2021	Permanent	0

[v-yanjiang-msft · Accepted solution]

Hi @heatherkw ,

According to your description, I enriched my sample.

I create four scenarios.

1.The next Enrollment is the same PersonalID and ProgramType is Emergency Shelter and within 12 months.

2.The next Enrollment is the same PersonalID and ProgramType isn't Emergency Shelter or Transitional Housing and within 12 months.

3.The next Enrollment isn't the same PersonalID and ProgramType is Emergency Shelter and within 12 months.

4.The next Enrollment is the same PersonalID and ProgramType is Transitional Housing and within 12 months.

I modify the formula:

Column =
IF (
    [ExitType] = "Permanent",
    IF (
        MAXX (
            FILTER (
                'Table',
                'Table'[EnrollmentID]
                    = EARLIER ( 'Table'[EnrollmentID] ) + 1
            ),
            'Table'[ProgramType]
        )
            IN { "Emergency Shelter", "Transitional Housing" }
                && DATEDIFF (
                    'Table'[ExitDate],
                    MAXX (
                        FILTER (
                            'Table',
                            'Table'[EnrollmentID]
                                = EARLIER ( 'Table'[EnrollmentID] ) + 1
                        ),
                        'Table'[Enrollment Date]
                    ),
                    MONTH
                ) <= 12
                && 'Table'[PersonalID]
                    = MAXX (
                        FILTER (
                            'Table',
                            'Table'[EnrollmentID]
                                = EARLIER ( 'Table'[EnrollmentID] ) + 1
                        ),
                        'Table'[PersonalID]
                    ),
        1,
        0
    )
)

Get the result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[heatherkw]

Hello, someone marked this as a solution, but just letting you know it doesn't work in the real data. It's not returning the correct information at all when we have hundreds of thousands of records. 

[v-yanjiang-msft]

Hi @heatherkw ,

Could you please provide a sample, then I would know where it stuck and how to dig.

Best Regards,
Community Support Team _ kalyj

[heatherkw]

Hello! Thank you for this!

I have a question. How would I change this if I need to additional program types? For example, I need Transitional Housing as another option, in addition to 3 more.