Forum Discussion

scabral's avatar
scabral
Icon for Helper IV rankHelper IV
6 years ago
Solved

get max date column within group

Hi,   i have the following table with claim payment rows as follows:   Claim Id Location ID Policy ID Damage  Reserve Dt Reserve Amt 1 234 333 Fire 6-9-2014 200 1 234 333 Fi...
  • Anonymous's avatar
    Anonymous
    6 years ago
    // Say, your table is T.

[Max Reserve Date] = // calc column in T
var __claimId = T[Claim ID]
var __locationId = T[Location ID]
var __policyId = T[Policy ID]
var __result =
    MAXX(
        filter(
            T,
            T[Claim ID] = __claimId
            &&
            T[Location ID] = __locationId
            &&
            T[Policy ID] = __policyId
        ),
        T[Reserve Date]
    )
return
    __result
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

scabral , Try like

 

calculate(Max(Table[Reserve Dt]), allexcpet(Table, Table[Claim Id],Table[Location ID],Table[Policy ID]))

 

or try like

 

calculate(Max(Table[Reserve Dt]), filter(allselcted(Table), Table[Claim Id] = max(Table[Claim Id]) && Table[Location ID] = max(Table[Location ID])
&& Table[Policy ID] && max(Table[Policy ID]) ))

scabral's avatar
scabral
Icon for Helper IV rankHelper IV
6 years ago

hi amitchandak,

 

here is my final code, but it's giving me a max date of 10-31-2020 instead of the 8-28-2014 max date of the group (i am only running this for claim 1).  Any idea why it is giving me that 10-31-2020 date?  I'm not even sure where it is coming from...

 

EVALUATE
ADDCOLUMNS (
    SUMMARIZE (
        FILTER (
            'Claim Reserve Values',
            RELATED ( 'Claim Reserve'[Reserve Group ID] )
                IN { 1000007, 1000008 }
                    && RELATED ( 'Claim Reserve'[Reserve Status ID] ) = 2
                    && RELATED ( 'Claim Adjustment File'[Claim Number] ) = "434571"
        ),
        'Claim Reserve Values'[Claim ID],
        'Claim Reserve Values'[LocationKey],
        'Claim Reserve Values'[Policy ID],
        'Claim Reserve Values'[Damage ID],
        'Claim Reserve'[Reserve Peril ID],
        'Claim Adjustment File'[Date Reported],
        'Claim Reserve Date'[Claim Reserve Date]
    ),
    "Max Reserve Date",
        CALCULATE (
            MAX ( 'Claim Reserve Date'[Claim Reserve Date] ),
            ALLEXCEPT (
                'Claim Reserve Values',
                'Claim Reserve Values'[Claim Id],
                'Claim Reserve Values'[LocationKey],
                'Claim Reserve Values'[Policy ID]
            )
        )
) 

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    scabral , Seem correct , But try this in case of column

    calculate(Max(Table[Reserve Dt]), filter(Table, Table[Claim Id] = earlier(Table[Claim Id]) && Table[Location ID] = earlier(Table[Location ID])
    && Table[Policy ID] && earlier(Table[Policy ID]) ))

     

    Do necessary changes as per your script