Determine enrollments and re-enrollments with gaps in timeframes

[m_darby]

I have a dataset where customers can enroll in a service, stay for awhile, leave, and then come back. Given this sample data:

Customer ID	Enrolled
1	1/1/2020
1	2/1/2020
1	3/1/2020
1	6/1/2020
1	7/1/2020
1	10/1/2020
1	11/1/2020
2	2/1/2020
2	3/1/2020
2	4/1/2020

How do I determine how many enrollments each has (1 should show 3 enrollments, 2 should show 1 enrollment)? How do I determine the length of each enrollment? How do I determine how many are enrolled in a time-period, such as by quarter (Q1 should show both, Q2 should only show 2, etc.)? Any help is appreciated. Is this better suited for DAX?

[Icey · Accepted solution]

Hi @m_darby ,

Please checked the attached .pbix file.

Best Regards,

Icey

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

[m_darby]

This works. Not sure why it works, but it works. Thanks.

[m_darby]

The logic is that every time there is a gap in the dates, then the customer left the service and then came back. (The service is a monthly service.) Customer 1 has three enrollments: 1/1/2020, 6/1/2020, and 10/1/2020. Customer 2 has one enrollment on 2/1/2020. I need to figure out for each customer: how may enrollments each has (Customer 1 has 3, Customer 2 has 1), the average length of enrollment (Customer 1 has 7 months of enrollment and 3 enrollments for an average of 2 and 1/3 months; Customer 2 has 3 months of enrollment and one enrollment for an average of 3 months), and how many unique enrollments there are in a given timeframe (for 2020 there are 2; for Q1 and Q2 there are 2; for Q3-Q4 there is only 1).

[Icey]

Hi @m_darby ,

How do I determine how many enrollments each has (1 should show 3 enrollments, 2 should show 1 enrollment)? 

---

How do you get this? What's the logic?🤔

Best Regards,

Icey