cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
m_darby
Frequent Visitor

Determine enrollments and re-enrollments with gaps in timeframes

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

 

Customer IDEnrolled
11/1/2020
12/1/2020
13/1/2020
16/1/2020
17/1/2020
110/1/2020
111/1/2020
22/1/2020
23/1/2020
24/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?

 

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @m_darby ,

 

Please checked the attached .pbix file.

measures.JPG

 

 

Best Regards,

Icey

 

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

View solution in original post

4 REPLIES 4
Icey
Community Support
Community Support

Hi @m_darby ,

 

Please checked the attached .pbix file.

measures.JPG

 

 

Best Regards,

Icey

 

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

View solution in original post

m_darby
Frequent Visitor

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

m_darby
Frequent Visitor

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
Community Support
Community Support

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

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors