Comparison of Test Results Between Consecutive Periods in DAX

[MarkSmash]

Hello,

I'm looking for a DAX solution to compare test results for a student population between two consecutive periods. 

Sample PBIX file: https://drive.google.com/file/d/1xEXHiddu5k4D67z9ZYtkmjgQqrhv0f1A/view?usp=drive_link

Rough sample of a visual I'm looking for is below.

• X-axis shows the maximum Benchmark level of the student for the selected term for the students (Example, "2022-23 Middle of Year").
• Y-axis contains the percentage of students broken out by their maximum Benchmark level achieved for the following term ("2022-23 End of Year").

Calculation Considerations:

1. Individual students must have at least one result in both the period selected and the next period being compared.
   • Term order based on Term.Sort field.
   • Examples of students that should be excluded in sample PBIX due to this rule when comparing between 2022-23 Middle of Year and 2022-23 End of Year: Students 4, 5, 8.
2. A student may have multiple test scores in one or both terms being compared. End result should only take into account their best Benchmark result for each period.
   • Examples: Student 3 and Student 6 in sample PBIX have multiple records in a term.
     • Student 3 has two results in "2022-23 Middle of Year," only the "Below Benchmark" result should be utilized.
     • Student 6 has two results in "2022-23 End of Year," only the "Below Benchmark" result should be utilized.

Sample Model:

Thank you for any help you can provide!

[Anonymous · Accepted solution]

Hi @MarkSmash ,

I have made many attempts following your sample and you can check if the results are what you expect:

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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