How to create a calculated column " Flag" using KQL

Question

timestamp	identifier	EDD	ward
2022-03-04T09:00:00Z	ab1	2022-03-06T09:00:00Z	h1
2022-03-04T11:45:00Z	ab1	2022-03-07T09:00:00Z	h1
2022-03-05T11:45:00Z	ab1	2022-03-09T09:00:00Z	h1
2022-03-06T11:45:00Z	ab1	2022-03-09T09:00:00Z	G1
2022-03-04T11:45:00Z	xy	2022-03-09T09:00:00Z	A1
2022-03-04T09:00:00Z	bc	2022-03-07T09:00:00Z	S1
2022-03-06T11:45:00Z	abc	2022-03-14T09:00:00Z	G1
2022-03-05T09:00:00Z	bc	2022-03-12T09:00:00Z	S1
2022-03-07T11:45:00Z	xyz	2022-03-10T09:00:00Z	Z1
2022-03-04T11:45:00Z	def	2022-03-09T09:00:00Z	A1
2022-03-06T11:45:00Z	def	2022-03-09T09:00:00Z	R1
2022-03-07T11:45:00Z	def	2022-03-09T09:00:00Z	H1

For Every change in EDD for an identifier, it should flag 1

Expected output:

timestamp	identifier	EDD	ward	Flag
2022-03-04T09:00:00Z	ab1	2022-03-06T09:00:00Z	h1
2022-03-04T11:45:00Z	ab1	2022-03-07T09:00:00Z	h1	1
2022-03-05T11:45:00Z	ab1	2022-03-09T09:00:00Z	h1	1
2022-03-06T11:45:00Z	ab1	2022-03-09T09:00:00Z	G1
2022-03-04T11:45:00Z	xy	2022-03-09T09:00:00Z	A1
2022-03-04T09:00:00Z	bc	2022-03-07T09:00:00Z	S1
2022-03-06T11:45:00Z	abc	2022-03-14T09:00:00Z	G1
2022-03-05T09:00:00Z	bc	2022-03-12T09:00:00Z	S1	1
2022-03-07T11:45:00Z	xyz	2022-03-10T09:00:00Z	Z1
2022-03-04T11:45:00Z	def	2022-03-09T09:00:00Z	A1
2022-03-06T11:45:00Z	def	2022-03-09T09:00:00Z	R1
2022-03-07T11:45:00Z	def	2022-03-09T09:00:00Z	H1

Answer 1

You should use the prev() function:

<Your query>
| extend Flag = iff(EOD != prev(EOD), 1, 0)

Just note that in order for prev() to work, the input to the extend operator should be serialized, for eample, sorted by values in some column. This is because records in Kusto are not ordered.