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