Get Value associated with Max Date Per Group

[PowerUser123]

So my data would be structured as such:

I need to get the value associated per user based on the Max date.

So the result for User 1 would be 14 and the result for User 2 would be 2.

Ideally I would want this as a calculated column or in query editor

[v-zhangti · Accepted solution]

Hi, @PowerUser123 

You can try the following methods.

Column:

Max = 
IF (
    [Date]
        = CALCULATE (
            MAX ( 'Table'[Date] ),
            FILTER ( 'Table', [User] = EARLIER ( 'Table'[User] ) )
        ),
    [Value]
)

Max 2 = 
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        'Table',
        [Date]
            = CALCULATE (
                MAX ( 'Table'[Date] ),
                FILTER ( 'Table', [User] = EARLIER ( 'Table'[User] ) )
            )
            && [User] = EARLIER ( 'Table'[User] )
    )
)

These are the methods to find the maximum date correlation value.

Best Regards,

Community Support Team _Charlotte

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

[Tahreem24]

@PowerUser123 Create this measure:

Measure  =

VAR MaxDate_ = CALCULATE(MAX(UserTable[Date]),ALLEXCEPT(UserTable,UserTable[User]))

RETURN CALCULATE(SUM(UserTable[Value]),UserTable[Date]=MaxDate_)