Help calculating past X years/semesters aggregate data and compare it to this years/semesters data.

[sarthakgirdhar]

Hello Power BI/DAX champions, 

I need your help with writing a DAX measure. I have a table that looks like this:-

I have student grades across multiple semesters. If I simply do an aggregate of student grades across semesters, it looks something like this (semester_code 202410 translates to 2023/2024 Fall) :- 



The semester code values are 201710, 201720, 201730, 201810, 201820,...,202330, 202410, 202420.

What I now want is to write a DAX measure such that I have aggregated student grades for the last few semesters so that I can compare it with the most recent results. 

Thank you!

[Anonymous]

Hi @sarthakgirdhar ,

Based on my testing, please try the following methods:

1.Create the sample table.

2.Create the new column and change the type to number.

Column = LEFT('Table'[semester_code], 4)

3.Create the new measure to filter the column.

Past = 
var _max = MAX('Table 2'[Semester])
RETURN
CALCULATE(
        COUNT('Table'[course_grade]),
        FILTER(
            ALLEXCEPT('Table','Table'[semester_code], 'Table'[course_grade]),
            'Table'[Column] < _max
        )
    )

Current = 
var _max = MAX('Table 2'[Semester])
RETURN
CALCULATE(
        COUNT('Table'[course_grade]),
        FILTER(
            ALLEXCEPT('Table','Table'[semester_code], 'Table'[course_grade]),
            'Table'[Column] = _max
        )
    )

4.Drag the measure into the table visual. The result is shown below.

Best Regards,

Wisdom Wu

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

[sarthakgirdhar · Accepted solution]

Hi @Anonymous (Wisdom Wu), 

I very much appreciate your help. This isn't exactly what I was hoping for; although, I did resolve it in a very similar manner as how you did it 🙂

Thanks and regards,
Sarthak Girdhar

[Anonymous]

Hi @sarthakgirdhar ,

I am glad you have solved the problem. Please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Wisdom Wu