Attrition By Course and Fiscal Year

[D_413]

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

2015	101	1	A	A1
2015	102	2	A	A1
2016	201	1	B	B1
2016	202	3	A	A1
2016	203	4	A	A1
2016	204	4	A	A2
2017	301	1	C	C1
2017	302	5	B	B1

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

FiscalYear NewUnique ReturningUnique

2015	2	0
2016	2	1
2017	1	1

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

FiscalYear Program Took 1 Course Took 2 Courses

2016

A

1

1

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.

[v-shex-msft · Accepted solution]

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