dynamicpowerbidaxcalculated-columnscalculated-field

Time travelling! How to calculate student grade on the fly?


I have the following school data, but I want to plot a line chart of say, no. of grade 3 students in each academic year. I am not able to do that since I only have the current grade for this academic year and when I pass this current grade in the legend, I get the 'journey' of the current grade 3 students in each academic year instead of the true grade 3 in each academic year. In other words, I need to calculate the grades of these students on the fly. The desired outcome is a DAX measure that I can pass to Y axis to plot my line chart (no. of grade X against academic year). So I came up with a DAX for grade 12 (will replicate for other grades), but it does not seem to achieve what I need. There is, however, no error in the DAX.

My logic for the DAX is that provided the current academic year is 2022 since it is before 1 August 2023, and student with school ID 1234 is in grade 3. When the academic year is 2021, this student would be in grade 2, and in 2020 grade 1, and in 2019 grade 0, but because this student joined in grade 1, so I want the result to be null.

School ID Joining Grade Current Grade
1234 1 3
5678 2 3
9101 4
1213 4 6
Dynamic year 12 =
VAR CurrentYear = YEAR(TODAY())
VAR CurrentMonth = MONTH(TODAY())
VAR CurrentAcademicYear = IF(CurrentMonth < 8, CurrentYear - 1, CurrentYear)
VAR ChosenYear = SELECTEDVALUE('date_table'[Year])
VAR YearDifference = CurrentAcademicYear - ChosenYear
VAR CurrentGrade = MAXX(SUMMARIZE('student_table','student_table'[SchoolID],'student_table'[current_grade]), [current_grade])
VAR EnrolmentGrade = MAXX(SUMMARIZE('student_table','student_table'[SchoolID],'student_table'[joining_grade]), [joining_grade])
VAR Result =
    IF(
        NOT ISBLANK(EnrolmentGrade),
        IF(
            CurrentGrade - YearDifference = 12,
            1,
            IF(
                CurrentGrade - YearDifference < EnrolmentGrade,
                0,
                CurrentGrade - YearDifference
            )
        )
    )
RETURN
    Result

Thank you for your help in advance and appreciate your time in helping me :)


Solution

  • The simplest way to do this might be to explode each students record by year. So ID 1234 would become 3 records: {{1234, 1}, {1234, 2}, {1234, 3}}. This is best accomplished in PowerQuery with a new column using List.Generate.

    First, replace any blank values with the starting grade (e.g., 1). Ensure that both grade columns are number data types.

    List.Generate( 
        ()=> [grade = [Joining Grade], current_grade = [Current Grade]], 
        each [grade] <= [current_grade], 
        each [grade = [grade] + 1, current_grade = [current_grade]], 
        each [grade]
        )
    

    Here, we create a two-column record for each row (grade & current_grade) from [Joining Grade] & [Current Grade]. We continue until the current iteration of grade is no longer less than or equal to current_grade, and add 1 to the current iteration each time. Then we can expand the new column to create new rows.

    enter image description here

    We'll need to identify which year each record corresponds to, which should just be current_academic_year - [Current Grade] + [Grade]. Where current_academic_year is a user-entered parameter and [Grade] is the newly expanded column. I suggest using a user-entered parameter for the current year because relying on Date.Year(DateTime.LocalNow()) (the M equivalent of YEAR(TODAY())) can give you funky results if the dataset is refreshed in a new calendar year, but the source table is not updated.

    After you load this to the model, you are just going to want a Calendar table and a Grades table via DAX. It is best practice to always use a star-schema, creating a table for each dimension you will slice by.

    Calendar = VALUES( grade_data[Year] )
    
    Schools = VALUES( grade_data[School ID] )
    
    Grades = GENERATESERIES( 1, 12 ) //Generate series of grades {1...12}
    

    Note, I just created these via DAX to show the schema, but usually they are loaded from a data source as well with more fields instead (e.g., school name, address, etc.).

    Star Schema