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