topic Re: Time travelling! How to calculate student grade on the fly? in DAX Commands and Tips

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

PatChan — Fri, 02 Jun 2023 21:01:20 GMT

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 thist 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,

IF(

CurrentGrade - YearDifference < EnrolmentGrade,

CurrentGrade - YearDifference

)

RETURN

Result

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

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

some_bih — Sun, 04 Jun 2023 20:11:15 GMT

Hi PatChan "thinking in DAX" is not easy 🙂 As I understand your grade values depend on two criterias: date when student join and another possible criteria to check if what date is current academic year (the current academic year is 2022 since it is before 1 August 2023)? Each student ID got date of joining?

provide some sample data and expected output with examples.

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

Anonymous — Tue, 06 Jun 2023 07:43:27 GMT

Hi PatChan ,

I suggest you to create two unrelated tables DimYear and DimGrade to help calculation.

Line Point = VAR _1 = GENERATE ( GENERATE ( ALL(DimYear), ALL(DimGrade) ), CALCULATETABLE(VALUES ( 'Table'[School ID] ),ALL('Table') )) VAR _2 = FILTER ( _1, VAR _START = CALCULATE ( SUM ( 'Table'[Joining Grade] ), FILTER ( 'Table', 'Table'[School ID] = EARLIER ( [School ID] ) ) ) VAR _END = CALCULATE ( SUM ( 'Table'[Current Grade] ), FILTER ( 'Table', 'Table'[School ID] = EARLIER ( [School ID] ) ) ) RETURN [Grade] >= _START && [Grade] <= _END ) VAR _3 = ADDCOLUMNS ( _2, "Correct Year", YEAR ( TODAY () ) - RANKX ( FILTER ( _2, [School ID] = EARLIER ( [School ID] ) ), [Grade], , DESC, DENSE ) + 1 ) VAR _4 = FILTER ( _3, [Year] = [Correct Year] ) RETURN SUMX(FILTER(_4,[Grade] = MAX(DimGrade[Grade]) && [School ID] = MAX('Table'[School ID])),[Correct Year])

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

PatChan — Fri, 09 Jun 2023 04:58:26 GMT

Anonymous Thank you for evening sending me the file. This is a very interesting way to approach the problem. However, I foresee this approach will be problematic if I have 10k+ students because there will be too many lines. Ideally, the Y axis should be the count of student and X axis is the year. This, accompanied by a grade filter so that when I filter for grade 12, the line chart will show the number of true grade 12 students as of different year.

Quick reminder why we have this problem is because the existing grade column in the dataset is the current grade. So when we filter grade 12 using this column, it will show the correct number of grade 12 this year (AY2022), but for the earlier years (e.g., AY2021) they will all be wrong because the AY2021 number should instead show the number of grade 12 in AY2021 (which are now grade 13 in AY2022), but this group of students will not be shown because of the existing filter of grade 12. Hope this clarifies.

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

Anonymous — Thu, 15 Jun 2023 05:58:57 GMT

Hi PatChan ,

Could you share a sample file with me and show me a screenshot with me? This will make it easier for me to find the solution. If your data size is large, please optmize your data model to get better performance.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

PatChan — Mon, 26 Jun 2023 08:00:56 GMT

Update: Luke_0 has kindly come up with a solution that solved the problem!
dynamic - Time travelling! How to calculate student grade on the fly? - Stack Overflow