get max date column within group

[scabral]

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	Fire	6-29-2014	500
1	234	333	Buildings	8-28-2014	1000
2	567	444	Fire	1-1-2014	600
2	567	444	Stock and Supplies	6-3-2014	750

I want to return the table with a new column added to the end that stores the max date for each claim id, location id and policy id.  So based on the data above i would want to return the following:

Claim Id	Location ID	Policy ID	Damage	Reserve Dt	Reserve Amt	Max Reserve Dt
1	234	333	Fire	6-9-2014	200	8-28-2014
1	234	333	Fire	6-29-2014	500	8-28-2014
1	234	333	Buildings	8-28-2014	1000	8-28-2014
2	567	444	Fire	1-1-2014	600	6-3-2014
2	567	444	Stock and Supplies	6-3-2014	750	6-3-2014

[Anonymous · Accepted solution]

// 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]

@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]

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]

@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