calculate the date difference between rows by group

[Anonymous]

Hi everyone

I have a table and I am trying to find the date difference by order ID between stages of an order.

I would like a column which tells me the number of days an order waits at each stage. As an example order_ID 1 was at stage A on 01 Jan and then stage B at 14 Jan, therefore the number of days at stage A is 13 days.

your assistance is appreciated

order_ID	stage	date allocated	Days  at stage
1	A	1/01/2022 0:00	13
1	B	14/01/2022 0:00	1
1	C	15/01/2022 0:00	date now - date allocated
2	A	2/01/2022 0:00
2	C	6/01/2022 0:00

[amitchandak · Accepted solution]

@Anonymous , New Column

=

Datediff([Date], minx(filter(Table, [order_ID] =earlier([order_ID] ) && [Date] > earlier([Date]) ) , [Date]), Day)

[Anonymous]

thank you