Forum Discussion

PowerUser123's avatar
PowerUser123
Icon for Helper II rankHelper II
4 years ago
Solved

Get Value associated with Max Date Per Group

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

Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    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.

2 Replies

  • Tahreem24's avatar
    Tahreem24
    Icon for Super User rankSuper User
    4 years ago

    PowerUser123 Create this measure:

    Measure  =
    VAR MaxDate_ = CALCULATE(MAX(UserTable[Date]),ALLEXCEPT(UserTable,UserTable[User]))
    RETURN CALCULATE(SUM(UserTable[Value]),UserTable[Date]=MaxDate_)


     
  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    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.