Forum Discussion
Measure for Unique Records from Previous Term
Hello,
I'm looking to get a measure, "Unique Students Last Year," which would be the number of unique records from the previous term (in this case a term is a school year). The scores are based on the school year, and aren't tied to a specific date for regular time intelligence functions.
End result wanted in a table:
| School Year Name | Unique Students | Unique Students Last Year |
| 2020-21 | 4 | |
| 2021-22 | 2 | 4 |
| 2022-23 | 1 | 2 |
Simplified model:
Sample Data:
SchoolYear table:
| SchoolYearKey | School Year Name | Order |
| 1 | 2020-21 | -2 |
| 2 | 2021-22 | -1 |
| 3 | 2022-23 | 0 |
TestScores table:
| ScoreKey | Score | Student | Subject | SchoolYearKey |
| 1 | 10 | 1001 | Math | 1 |
| 5 | 15 | 1001 | Math | 2 |
| 4 | 16 | 1004 | Math | 1 |
| 3 | 17 | 1003 | Math | 1 |
| 8 | 18 | 1001 | English | 1 |
| 2 | 20 | 1002 | Math | 1 |
| 6 | 20 | 1002 | Math | 2 |
| 7 | 20 | 1001 | Math | 3 |
| 9 | 20 | 1001 | English | 2 |
Thanks!
Click here to download the solution
Download PBIX
How iit works ...Students this year = DISTINCTCOUNT(TestScores[Student])Students previous year = VAR thisref= SELECTEDVALUE(SchoolYear[Order]) VAR previousref= thisref - 1 RETURN CALCULATE( DISTINCTCOUNT(TestScores[Student]), ALL(SchoolYear), SchoolYear[Order] = previousref)Please click both the ACCCEPT SOLUTION button and the thumbs up buttons.
If you need more help then please raise a new ticket and quote speedramps
I will receive an automated notifcation and will try help
3 Replies
speedrampsSuper User
Click here to download the solution
Download PBIX
How iit works ...Students this year = DISTINCTCOUNT(TestScores[Student])Students previous year = VAR thisref= SELECTEDVALUE(SchoolYear[Order]) VAR previousref= thisref - 1 RETURN CALCULATE( DISTINCTCOUNT(TestScores[Student]), ALL(SchoolYear), SchoolYear[Order] = previousref)Please click both the ACCCEPT SOLUTION button and the thumbs up buttons.
If you need more help then please raise a new ticket and quote speedramps
I will receive an automated notifcation and will try helpAhh--All(SchoolYear) was the missing piece for me. Thanks for the quick response!
- speedramps
Thank you for the kudos.
If you need more help then raise a new ticket and quote speedramps anywhere in the text, I will then receive an automatic notification and will be delighted to help you again.
Please always try provide example input data as table text (not a screen print) so we can import the data to build a solution for you. You will gain respect and a much quicker and better responses with the more effort put in to describing problems
