Comparing Values Within The Same Table

[deanbland]

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 ID	Form	Form Created Date
123	BCR1a	22/10/2019 10:49:01
123	BCR1a	22/10/2019 10:49:01
123	BCR1a	24/09/2020 12:47:34
123	BCR1b	22/09/2021 15:08:09
456	BCR1b	22/10/2020 09:56:34
456	BCR1b	05/05/2021 16:55:03

How would I go about doing this, please?

[Ashish_Mathur]

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]

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"

[amitchandak]

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

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 · Accepted solution]

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