topic Re: get max date column within group in DAX Commands and Tips

get max date column within group

scabral — Tue, 29 Sep 2020 00:03:11 GMT

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

Re: get max date column within group

amitchandak — Tue, 29 Sep 2020 01:56:11 GMT

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

Re: get max date column within group

scabral — Tue, 29 Sep 2020 03:01:56 GMT

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

Re: get max date column within group

amitchandak — Tue, 29 Sep 2020 03:52:14 GMT

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

Re: get max date column within group

Anonymous — Tue, 29 Sep 2020 09:29:10 GMT

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