Compare and lookup date in same table

[char23]

Hello, I am trying to add a calculated column to list "deleted" for any ID that does not show in the most recent entry (1). What is the best way to approach this? I tried creating with using if statements, but could not get it to work. I am trying to add the column in green. 

ID	ENTRY	Deleted IDs
A	2
B	2
C	2	Deleted
D	2
A	1
B	1
D	1

[FreemanZ]

Hi @char23 ，

try to add a column like:

Column = 
VAR _entry = [entry]
VAR _id = [id]
VAR _entrypre =_entry - 1
VAR _idlistpre = CALCULATETABLE(VALUES(data[id]), data[entry] = _entrypre, ALL())
VAR _result = IF( _id in _idlistpre || ISEMPTY(_idlistpre), BLANK(), "Deleted")
RETURN  _result

it worked like:

[char23]

What if I want to ignore any value in entry column greater than 2. How would I modify? 

ID	ENTRY	Deleted IDs
A	3
B	3
C	3
D	3
A	2
B	2
C	2	Deleted
D	2
A	1
B	1
D	1

[FreemanZ · Accepted solution]

hi @char23 ,

try like:

Column =

VAR _entry = [entry]

VAR _id = [id]

VAR _entrypre =_entry - 1

VAR _idlistpre = CALCULATETABLE(VALUES(data[id]), data[entry] = _entrypre, ALL())

VAR _result = IF( _id in _idlistpre || ISEMPTY(_idlistpre) || _entry>2 , BLANK(), "Deleted")

RETURN  _result

[Irwan]

hello @char23 

please check if this accomodate your need.

Deleted IDs =
var _ID = 'Table'[ID]
var _Duplicate = COUNTROWS(FILTER('Table','Table'[ID]=_ID))
Return
IF(
    _Duplicate=1,
    "Deleted",
    ""
)

The idea is you want to look for duplicate. If no duplicate (count value is 1), then you will have string "Deleted".

Hope this will help you.

Thank you.

[char23]

What if I have more than two values in my entry column and want to ingnore anything greater than 2. How would I modify? 

ID	ENTRY	Deleted IDs
A	3
B	3
C	3
D	3
A	2
B	2
C	2	Deleted
D	2
A	1
B	1
D	1

[Irwan]

hello @char23 

modify the conditional if statement should do.

Deleted IDs =
var _ID = 'Table 2'[ID]
var _Duplicate = COUNTROWS(FILTER('Table','Table'[ID]=_ID))
Return
IF(
    _Duplicate=1&&'Table 2'[ENTRY]<=2,
    "Deleted",
    ""
)

Adding 'ENTRY' less than or equal 2 in if statement.

Hope this will help you.

Thank you.

[char23]

Do you use two different tables in this solution? I tried this method, but I am getting deleted by all new IDs where ENTRY = 1. But I want ID C where ENTRY = 2 to show deleted because ID C is not found in the group where ID=1. 

ID	ENTRY	Deleted IDs
A	3
B	3
C	3
D	3
E	3
A	2
B	2
C	2
D	2
A	1
B	1
D	1
G	1	Deleted
H	1	Deleted