Forum Discussion

deanbland's avatar
deanbland
Helper III
5 years ago
Solved

Comparing Values Within The Same Table

Hi, 

 

I am wanting to create a column that assigns a 1 next to the most recent entry within my dataset per Form. So from the data below, the 3rd and 4th entry would have a 1 assigned as would the last entry. 

 

Opportunity IDForm Form Created Date
123BCR1a

22/10/2019 10:49:01

123BCR1a22/10/2019 10:49:01
123BCR1a24/09/2020 12:47:34
123BCR1b22/09/2021 15:08:09
456BCR1b22/10/2020 09:56:34
456BCR1b05/05/2021 16:55:03

 

How would I go about doing this, please?

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    Try this calculated column formula

    =1*(CALCULATE(MAX(Data[Form Created Date]),FILTER(Data,Data[Opportunity ID]=EARLIER(Data[Opportunity ID])&&Data[Form]=EARLIER(Data[Form])))=Data[Form Created Date])

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    deanbland , Either 4th or 4 and 6th can be, not sure how is the 3 rd entry is max too

     

    Try a new column

     

    New column =
    var _max = maxx(Table, [Form Created Date])
    return
    if(_max =[Form Created Date] ,1,0)

     

     

    or

     


    New column =
    var _max = maxx(filter(Table,[OpportunityId] =earlier([OpportunityId])), [Form Created Date])
    return
    if(_max =[Form Created Date] ,1,0)

    • deanbland's avatar
      deanbland
      Helper III
      5 years ago

      Hi, thanks for the quick response 

       - I should have been a little clearer...

       

      The Form type, (i.e., BCR1a/BCR1b) needs to be taken into account. Per Opportunity ID there can be multiple Form types and so I need to be able to say what the latest Form type is per Opportunity ID. So from my example, these should be the ones highlighted: 

      Opportunity ID       Form        Form Created Date

      - 123                       BCR1a       24/09/2020

      -123                        BCR1b       22/09/2021

      -456                        BCR1b       05/05/2021

       

      Hope this makes more sense.

       

      P.s. I couldn't post this in a table as I was accused of "Post Flooding

       

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        5 years ago

        Hi,

        Try this calculated column formula

        =1*(CALCULATE(MAX(Data[Form Created Date]),FILTER(Data,Data[Opportunity ID]=EARLIER(Data[Opportunity ID])&&Data[Form]=EARLIER(Data[Form])))=Data[Form Created Date])
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    This calculated column formula works

    =1*(CALCULATE(MAX(Data[Form Created Date]),FILTER(Data,Data[Opportunity ID]=EARLIER(Data[Opportunity ID])))=Data[Form Created Date])

    Hope this helps.

    • deanbland's avatar
      deanbland
      Helper III
      5 years ago

      Hi, thanks for the quick response - I should have been a little clearer...

       

      The Form type, (i.e., BCR1a/BCR1b) needs to be taken into account. Per Opportunity ID there can be multiple Form types and so I need to be able to say what the latest Form type is per Opportunity ID. So from my example, these should be the ones highlighted: 

      Opportunity ID       Form        Form Created Date

      - 123                       BCR1a       24/09/2020

      -123                        BCR1b       22/09/2021

      -456                        BCR1b       05/05/2021

       

      Hope this makes more sense.

       

      P.s. I couldn't post this in a table as I was accused of "Post Flooding"