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 IdLocation IDPolicy IDDamage Reserve DtReserve Amt
1234333Fire6-9-2014200
1234333Fire6-29-2014500
1234333Buildings8-28-20141000
2567444Fire1-1-2014600
2567444Stock and Supplies6-3-2014750

 

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 IdLocation IDPolicy IDDamage Reserve DtReserve AmtMax Reserve Dt
1234333Fire6-9-20142008-28-2014
1234333Fire6-29-20145008-28-2014
1234333Buildings8-28-201410008-28-2014
2567444Fire1-1-20146006-3-2014
2567444Stock and Supplies6-3-2014750

6-3-2014

  • 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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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