Forum Discussion
Average Daily Subscription over time period
I am searching for advice on a variation of the count-active-subscribers issue that has been otherwise well-addressed in this forum. I am looking for a way to count the average of daily active enrollments over a variable time period. ("enrollment" is effectively like subscription, where there is a start date and potentially an end date.)
I can get the total number of active enrollments during a time frame with this measure:
This gets me the total number of active enrollments over a time frame (depending on context - typically a month) However, I am looking for the average of the daily active enrollments, if that makes sense. So, an average of the enrollment count for the (1st, 2nd, 3rd .... last day of month) - NOT necessarily the total active enrollments over the whole month.
I am trying to avoid creating a permanent snapshot table because of space constraints. Is there a different strategy that would work? I am fairly new to all of this, so perhaps there is an easy solution that I cannot locate. Thanks in advance.
After significant trial and error - and some hints from the above posts, I was able to find a solution. It works out to be similar to the the "events in progress" dax pattern found here - Events in progress – DAX Patterns Thank you to anyone who helped.
5 Replies
Hi J_Melton_TX ,
Please try like:
Total No of Active Enrollments by Date = VAR Currentdate = MAX ( date_DIM_enrollment_creation[Date] ) VAR Startdate = MIN ( date_DIM_enrollment_creation[Date] ) VAR Days = DATEDIFF ( Startdate, Currentdate, DAY ) VAR ActiveEnrollments = CALCULATE ( COUNTROWS ( class_enrollment_FACT ), ALL ( date_DIM_enrollment_creation ), date_DIM_enrollment_creation[Date] <= Currentdate, ISBLANK ( class_enrollment_FACT[drop_date] ) || class_enrollment_FACT[drop_date] >= Startdate ) VAR _Avg = DIVIDE ( ActiveEnrollments, Days ) RETURN _AvgBest Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
Thank you so much for your post. This is a step in the right direction for sure.
Let me do a better job (hopefully) of explaining what I am trying to do. We have a situation where people can enroll or drop classes at any point. They may be enrolled in multiple different classes at the same time, or they may drop one class and add another mid-month.
What I am trying to come up with is a way to find the average number of enrollments that were active per day over a given time period. Basically, if the time period is a month, adding up how many were active on the 1st, how many were active on the second, .... all the way to the end of the month - then dividing by the number of days.
I can get the total enrollments that were active at any point (which was my measure listed above.) If someone drops one class and enrolls in another, that counts them twice when they were only ever active in a single class at once. I can also count the number of unique students that were enrolled, but this doesn't reflect when a student may be enrolled in multiple classes at once.
I need a way to iterate through the days in the time period, adding up the active enrollments for each day, then dividing that total by the number of days. I can get the number of days (as the above post has helpfully shown), I just can't seem to figure out how to get the sum of each separate day.- Ashish_Mathur
Super User
Hi,
Share some data to work with (in a format that can be pasted in an MS Excel file) and show the expected result.
I appreciate anyone who has looked at this. I am stuck at this point. I thought I had an inspiration to try to get the answer a different way, but I can't seem to make it work. I am more than a little past my knowledge level at this point. I will throw my latest attempt out here to see if anyone has a suggestion. Thank in advance for any help.
Adjusted Enrollment by Date =// Determine Average Enrollment which factors/corrects for enrollments starting late or ending earlyVAR StartDate =MIN ( date_DIM_enrollment_creation[Date] )VAR EndDate =MAX ( date_DIM_enrollment_creation[Date] )// Calculate total days missed at the start of the period due to enrollments after start dateVAR MissedStartDays =SUMX(Class_enrollment_FACT,DATEDIFF ( class_enrollment_FACT[created], StartDate, DAY))VAR TotalMissedStartDays =CALCULATE (MissedStartDays,class_enrollment_FACT[created] > StartDate,class_enrollment_FACT[created] <= EndDate)// Calculate total days missed at end of period due to dropping before end dateVAR MissedEndDays =SUMX(class_enrollment_FACT,DATEDIFF ( class_enrollment_FACT[drop_date], EndDate, DAY))VAR TotalMissedEndDays =CALCULATE (MissedEndDays,class_enrollment_FACT[drop_date] >= StartDate,class_enrollment_FACT[drop_date] < EndDate)// Determine total days available during periodVAR TotalAvailableDays =DATEDIFF ( StartDate, EndDate, DAY )// Count total enrollments active at any point during time frameVAR ActiveEnrollments =CALCULATE (COUNTROWS ( class_enrollment_FACT ),ALL ( date_DIM_enrollment_creation ),date_DIM_enrollment_creation[Date] <= EndDate,ISBLANK ( class_enrollment_FACT[drop_date] )|| class_enrollment_FACT[drop_date] >= Startdate)// Determine Percent of Max Enrollment Days and Adjust Total Enrollment AccordinglyVAR FullEnrollmentDays = TotalAvailableDays * ActiveEnrollmentsVAR EnrollmentRatio =DIVIDE (FullEnrollmentDays - MissedStartDays - MissedEndDays,FullEnrollmentDays,BLANK ())VAR AdjustedTotalEnrollment = ActiveEnrollments * EnrollmentRatioRETURNAdjustedTotalEnrollmentAfter significant trial and error - and some hints from the above posts, I was able to find a solution. It works out to be similar to the the "events in progress" dax pattern found here - Events in progress – DAX Patterns Thank you to anyone who helped.
