CODEWITHSUNDEEP
datepowerbidax
Pat
Pat

Reputation: 47

Plot a line chart with 3 lines for number of current student, new joiner, and leavers against the X-axis showing year-month

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 dwh d_student table

I would like to have the following table as the output ouput table with the required columns for plotting the line chart

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 :(

Upvotes: 0

Views: 38

Answers (1)

Pat
Pat

Reputation: 47

An update on this question. I found the following DAX 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

Upvotes: 0

Related Questions