Calculated column based maximum value of another column for a set of data

[belvoir99]

Student	Score	Term	Year	Class	CalendarYear	LatestYear	NewYear
John	3	1	1	Swallows	2020	2020	2
James	2	1	2	Swallows	2020	2020	2
Jane	4	1	2	Blackbird	2020	2020	3
Mary	3	1	3	Blackbird	2020	2020	3
John	4	2	1	Swallows	2020	2020	2
James	2	2	2	Swallows	2020	2020	2
Jane	5	2	2	Blackbird	2020	2020	3
Mary	3	2	3	Blackbird	2020	2020	3
John	3	1	1	Swallows	2019	2020	1
James	2	1	2	Swallows	2019	2020	2
Jane	4	1	2	Blackbird	2019	2020	2
Mary	3	1	3	Blackbird	2019	2020	3

There are 4 pupils: John and James are in the same class Swallows but not in the same Year. In other words, the Swallows class contains two Years.

Similarly, Jane & Mary are in the Blackbirds class but in Years 2 and 3 respectively.

I need a NewYear column, in a more complex example, whereby we put all students into the 'higher' class but only if the CalendarYear is the LatestYear

So, in 2020 Calendar Year, John is moved from Year 1 to Year 2 and Jane is moved from Year 2 to Year 3.

I need a column as it is going on the X axis of a bar chart.

I can't use Power Query as the Class is selected on a Slicer (at least I think that is right).

Thus, (I think!) I want to return the maximum value of the Year for that Class if the CalendarYear = LatestYear, otherwise just return the Year

I’ve tried various combinations of MAX, MAXX, FILTER and EARLIER but have got completely stuck!

[v-jingzhang · Accepted solution]

Hi @belvoir99 

Please try the DAX code below.

Column = 
VAR class = Students[Class]
VAR maxYear = MAXX ( FILTER ( Students, Students[Class] = class ), Students[Year] )
RETURN
    IF ( Students[CalendarYear] = Students[LatestYear], maxYear, Students[Year] )

Best Regards,

Community Support Team _ Jing Zhang

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

[belvoir99]

@v-jingzhang - thanks so much! Works perfectly! 😀

[harshnathani]

Hi @belvoir99 ,

Can you share the expected output column too in the dataset shared

Regards,

HN

[belvoir99]

hi @harshnathani 

It's the last column on the right called NewYear which I want to create. Hope that helps.

Thanks.