Forum Discussion
Solved
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 enro...
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.
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 early
VAR 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 date
VAR 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 date
VAR 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 period
VAR TotalAvailableDays =
DATEDIFF ( StartDate, EndDate, DAY )
// Count total enrollments active at any point during time frame
VAR 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 Accordingly
VAR FullEnrollmentDays = TotalAvailableDays * ActiveEnrollments
VAR EnrollmentRatio =
DIVIDE (
FullEnrollmentDays - MissedStartDays - MissedEndDays,
FullEnrollmentDays,
BLANK ()
)
VAR AdjustedTotalEnrollment = ActiveEnrollments * EnrollmentRatio
RETURN
AdjustedTotalEnrollment