Dynamic Max Value and then a sum - please help!

[Anonymous]

hi,

Hoping that someone can help, as this one is breaking my brain. I have data related to students enrolment in college. There is a row of data for each student, for each year that the student is enrolled with the college. The students take different lengths of time to complete their degrees.  So one student may take three years (three rows of data); another student may take one year (one row of data), etc.

I want to be able to produce the latest row of data for each student, relative to a year selected by filter, and the aggregate the value up to Group. Example data set below:

Group	Student	Year	Value
Green	Student1	2018	0
Green	Student1	2019	0
Green	Student1	2020	1
Green	Student2	2018	1
Green	Student3	2018	0
Green	Student3	2019	1
Green	Student3	2020	1
Green	Student3	2021	0

So, if I select the year 2020 as the filter, I would like the table to output the following:

Group	Student	Year	Value
Green	Student1	2020	1
Green	Student2	2018	1
Green	Student3	2020	1

Then, I would like to sum that for Green group, so that I would get Green = 3. I have written a measure which works, in that I can flag the latest row of data, and it does this dynamically depending on what year I have selected. However when I aggregate the data to 'Group', the measure goes with it and delivers a value of 1 rather than a value of 3. I need to somehow get the calculation to stick at a student aggregation and then sum to 'Group'. If that made any sense at all, can someone help?!

For ref, here is the measure I wrote:

Latest Year =

CALCULATE(

MAX('Table'[Year]),

ALLEXCEPT('Table','Table'[Student],'Table'[Year]))

Many thanks

[Anonymous]

Thanks so much! I think this is doing the trick!! I'll need to do a bit more tweaking and testing, as the real data is slightly more complex than the sample data given above, but I think that's working now and is certainly summing at the student level which is what I am after. Apologies for the delay in responding, and many thanks for your help.

[Anonymous]

Sorry, the formatting looks hellish on my Tables!! The student values are 'Student1', 'Student2', etc, and the Year values are '2020', '2019' etc. Hopefully you can make some sense of it.

[Icey · Accepted solution]

Hi @Anonymous ,

Is this what you want?

Year Slicer = VALUES('Table'[Year])

Max Year = 
CALCULATE (
    MAX ( 'Table'[Year] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Student] ),
        'Table'[Year] <= SELECTEDVALUE ( 'Year Slicer'[Year] )
    )
)

Measure = 
IF ( MAX ( 'Table'[Year] ) = [Max Year], 1 )

Best Regards,

Icey

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