cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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 Result =
IF(
IF(
CurrentGrade - YearDifference = 12,
1,
IF(
0,
)
)
)
RETURN
Result

Thank you for your help in advance and appreciate your time in helping me 🙂
5 REPLIES 5
Community Support

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] <= _END
)
VAR _3 =
_2,
"Correct Year",
YEAR ( TODAY () )
- RANKX (
FILTER ( _2, [School ID] = EARLIER ( [School ID] ) ),
,
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.

Frequent Visitor

@v-rzhou-msft 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.

Frequent Visitor

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

Community Support

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.

Super User

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!