Find the value from a related column for an aggregate?

[JohnHS]

In this case I want to find the date of the Max value of a Sum agregation.

In my example DevAdvance = Sum(Development[ADVANCE]) and the DateKey is from a Calendar table

where MAXX('Calendar',[DevAdvance]) = 57

I can see that the Max DevAdvance was on the 11-Apr-20 how can I return that date?

[mahoneypat · Accepted solution]

Not sure what context you are going to use this measure, but you could try an approach like this.

Date with Max =
VAR __summary =
    ADDCOLUMNS ( VALUES ( 'Calendar'[DateKey] ), "@DevAdvance", [DevAdvance] )
VAR __maxdevadv =
    MAXX ( __summary, [@DevAdvance] )
VAR __filtered =
    FILTER ( __summary, [@DevAdvance] = __maxdevadv )
RETURN
    MAXX ( __filtered, 'Calendar'[DateKey] )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[JohnHS]

Works perfectly thank you.