Forum Discussion

D_413's avatar
D_413
Regular Visitor
9 years ago
Solved

Attrition By Course and Fiscal Year

Hello,

 

I have an application query from a student data base and I'd like to calculate churn from fiscal year to fiscal year and returning program enrollment within a fiscal year. That date set looks like this:

 

FiscalYear ApplicationID StudentID Program Course

20151011AA1
20151022AA1
20162011BB1
20162023AA1
20162034AA1
20162044AA2
20173011CC1
20173025BB1

 

So I'd like to calculate both new and returning students for a fiscal year. Ex.

FiscalYear NewUnique ReturningUnique

201520
201621
201711

 

But also calculate that, for example, the unique invidividuals taking more than 1 course.

FiscalYear Program Took 1 Course Took 2 Courses

2016A11

 

 

I've been working with caluclated status column for year-to-year churn, but I admit to being stumped about what should be a simple count of Course.

 

Thanks for the help.

  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi D_413,

     

    Please refer to below formulas if it suitable for your requirement.

     

    Calculate table formulas:

    Analysis StudentID = 
SUMMARIZE (
    Records,
    [FiscalYear],
    "New Unique", COUNTROWS (
        EXCEPT (
            CALCULATETABLE (
                VALUES ( Records[StudentID] ),
                FILTER ( Records, [FiscalYear] = EARLIER ( [FiscalYear] ) )
            ),
            CALCULATETABLE (
                VALUES ( Records[StudentID] ),
                FILTER ( ALL ( Records ), [FiscalYear] < EARLIER ( Records[FiscalYear] ) )
            )
        )
    )
        + 0,
    "Returning Unique", COUNTROWS (
        INTERSECT (
            CALCULATETABLE (
                VALUES ( Records[StudentID] ),
                FILTER ( ALL ( Records ), [FiscalYear] < EARLIER ( Records[FiscalYear] ) )
            ),
            CALCULATETABLE (
                VALUES ( Records[StudentID] ),
                FILTER ( Records, [FiscalYear] <= EARLIER ( [FiscalYear] ) )
            )
        )
    )
        + 0
)

     

    Analysis Course = 
SUMMARIZE (
    Records,
    [FiscalYear],
    [Program],
    "Unique Course", DISTINCTCOUNT ( Records[Course] )
)

     

    Reference:

    EXCEPT Function (DAX)

    INTERSECT Function (DAX)

     

    Regards,

    Xiaoxin Sheng

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi D_413,

     

    Please refer to below formulas if it suitable for your requirement.

     

    Calculate table formulas:

    Analysis StudentID = 
SUMMARIZE (
    Records,
    [FiscalYear],
    "New Unique", COUNTROWS (
        EXCEPT (
            CALCULATETABLE (
                VALUES ( Records[StudentID] ),
                FILTER ( Records, [FiscalYear] = EARLIER ( [FiscalYear] ) )
            ),
            CALCULATETABLE (
                VALUES ( Records[StudentID] ),
                FILTER ( ALL ( Records ), [FiscalYear] < EARLIER ( Records[FiscalYear] ) )
            )
        )
    )
        + 0,
    "Returning Unique", COUNTROWS (
        INTERSECT (
            CALCULATETABLE (
                VALUES ( Records[StudentID] ),
                FILTER ( ALL ( Records ), [FiscalYear] < EARLIER ( Records[FiscalYear] ) )
            ),
            CALCULATETABLE (
                VALUES ( Records[StudentID] ),
                FILTER ( Records, [FiscalYear] <= EARLIER ( [FiscalYear] ) )
            )
        )
    )
        + 0
)

     

    Analysis Course = 
SUMMARIZE (
    Records,
    [FiscalYear],
    [Program],
    "Unique Course", DISTINCTCOUNT ( Records[Course] )
)

     

    Reference:

    EXCEPT Function (DAX)

    INTERSECT Function (DAX)

     

    Regards,

    Xiaoxin Sheng