Count the number of students if their admission or leaving date is in the same year-month

[PatChan]

I have a student data table called dwh d_student containing admission date column called txtAdmissionDate and leaving date column called txtLeavingDate. The data ingestion for this table is incremental instead of snapshotting every as_of_date. However, the requirement is to snapshot at different year-month on the X-axis, what's the number of current student, new joiners, and leavers with the help of just admission and leaving dates.

The X-axis of this line chart should have year-month starting from the earliest year-month of the txtAdmissionDate until the latest year-month of the txtLeavingDate.

The logic for the number of new joiner would be the count of student if the year-month on the X-axis is the same as the year-month of the admission date of each student in the dwh d_student table.

The logic for the number of leavers would be the count of student if the year-month on the X-axis is the same as the year-month of the leaving date of each student in the dwh d_student table.

Lastly, the logic for the number of current student would be the count of students whose year-month of the admission date of each student is before the year-month on the X-axis and the leaving date is either NULL or after the year-month on the X-axis.

For example, my dwh d_student table has the following data

I would like to have the following table as the output

Below are what I have tried:

date_table =
ADDCOLUMNS (
    CALENDAR (
        MIN ( d_student[txtAdmissionDate] ),
        MAX ( d_student[txtLeavingDate] )
    ),
    "YearMonth", FORMAT ( [Date], "yyyy-mm" )
)

new_joiners =
CALCULATE (
    COUNTROWS ( d_student ),
    FILTER (
        d_student,
        FORMAT ( d_student[txtAdmissionDate], "yyyy-mm" )
            = SELECTEDVALUE ( date_table[YearMonth] )
    )
)

leavers =
CALCULATE (
    COUNTROWS ( d_student ),
    FILTER (
        d_student,
        FORMAT ( d_student[txtLeavingDate], "yyyy-mm" )
            = SELECTEDVALUE ( date_table[YearMonth] )
    )
)

current_students =
CALCULATE (
    COUNTROWS ( d_student ),
    FILTER (
        d_student,
        d_student[txtAdmissionDate] <= LASTDATE ( date_table[Date] )
            && (
                d_student[txtLeavingDate] >= FIRSTDATE ( date_table[Date] )
                    || ISBLANK ( d_student[txtLeavingDate] )
            )
    )
)

but some of these DAX just throwing up errors 😞

[v-shex-msft]

HI @PatChan,

Since I'm not so clear about these error messages, can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

In addition, you can also take a look at the following blog blog 'start date', 'end date 'parts about date range defined based on multiple date fields analysis if it helps:

Before You Post, Read This: start/end date 

Regards,

Xiaoxin Sheng

[PatChan]

Hi @v-shex-msft,

Thanks for following up with this and pointing me to the relevant resources. There were countless attempts with those DAX (some provided by ChatGPT) with various error messages, which I should have captured some of them. 

I, however, found the following DAX work and tested that they work:

No. of current students =
VAR CurrDate =
    SELECTEDVALUE ( 'date table'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'student table'[student_ID] ),
        AND (
            ISONORAFTER ( 'student table'[yearmonth_enrolment], CurrDate, DESC ),
            OR (
                ISAFTER ( 'student table'[yearmonth_leaving], CurrDate, ASC ),
                ISBLANK ( 'student table'[yearmonth_leaving] )
            )
        )
    )





No of new joiners = var CurrDate = SELECTEDVALUE('date table'[Date])

return CALCULATE(DISTINCTCOUNT('student table'[student ID]), 'student table'[yearmonth_enrolment] = CurrDate)

No of leavers = var CurrDate = SELECTEDVALUE('date table'[Date])

return CALCULATE(DISTINCTCOUNT('student table)'[student ID], 'student table'[yearmonth_leaving] = CurrDate)

Hopefully these DAX will help others who face similar business needs!

[v-shex-msft]

HI @PatChan,

I'm glad to hear you find the solution and sharing the sample code here, they should help others who has the similar requirement.

Regards,

Xiaoxin Sheng