cancel
Showing results for
Did you mean:
Helper I

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

 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!

1 ACCEPTED SOLUTION
Community Support

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.

4 REPLIES 4
Community Support

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.

Helper I

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

Super User III

Hi @belvoir99 ,

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

Regards,

HN

Helper I

hi @harshnathani

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

Thanks.

Announcements