Check if a value exists previously

[actroyani]

Hi,

I would like to know how to create a new column where I get the value "New" if a person exists in previous months of my data and "Old" if it exists in previous months or if it's the first month of data.

I have a table like this:

Date	Person
15/11/2021	A
13/11/2021	A
12/11/2021	B
10/11/2021	C
20/10/2021	A
15/10/2021	C
10/10/2021	C
15/09/2021	A

Take into account that individuals may appear more than once on a month, so this is what I would like:

Date	Person	Column
15/11/2021	A	Old
13/11/2021	A	Old
12/11/2021	B	New
10/11/2021	C	Old
20/10/2021	A	Old
15/10/2021	C	New
10/10/2021	C	New
15/09/2021	A	New

Thanks in advance!

[JRo]

@amitchandak 

and if I want to check if it is NEW or OLD but only in last 5 weeks? So the range of date is 5 weeks before [Date]?

[amitchandak · Accepted solution]

@actroyani ,

a new column =

var _1 = countx(filter(Table, [Person] = earlier([Person]) && [Date] < earlier([Date] ) ), [Person]) +0

return

if(_1 > 0, "Old", "New")