topic Re: Help calculating past X years/semesters aggregate data and compare it to this years/semesters data. in DAX Commands and Tips

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

sarthakgirdhar — Wed, 03 Jul 2024 15:39:34 GMT

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!

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

Anonymous — Thu, 04 Jul 2024 09:56:19 GMT

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.

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

sarthakgirdhar — Thu, 11 Jul 2024 18:12:02 GMT

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

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

Anonymous — Fri, 12 Jul 2024 01:59:04 GMT

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