Time difference between rows grouped by value

[adeptus2008]

Hi there, 

I have a table like that:

account_id	orders_id	orders_cdate	orders_expdate	solution
218966	1073732	22.05.2018	22.05.2019	new
281343	1099694	30.11.2018	30.11.2019	new
326276	1125032	30.01.2019	23.01.2020	new
218966	1285798	08.01.2020	08.01.2021	update
326276	1293403	23.01.2020	23.01.2021	update
281343	1296352	29.01.2020	12.04.2021	update

I want to calculate date difference in days, between [orders_cdate] and [orders_expdate] of previous order within the group of unique account_id. How can I handle this? Thanks for any ideas.

My desrired table with calculated column would be

account_id	orders_id	orders_cdate	orders_expdate	solution	day_diff
218966	1073732	22.05.2018	22.05.2019	new	0
281343	1099694	30.11.2018	30.11.2019	new	0
326276	1125032	30.01.2019	23.01.2020	new	0
218966	1285798	08.01.2020	08.01.2021	update	-231
326276	1293403	23.01.2020	23.01.2021	update	0
281343	1296352	29.01.2020	12.04.2021	update	-60

[amitchandak · Accepted solution]

Try. I think you want the second one.

date diff orders_cdate= datediff(table[orders_cdate],maxx(filter(table,table[account_id] =earlier(table[account_id]) && table[orders_id] 
							<earlier(table[orders_id])),table[	orders_cdate]),DAY)
							
date diff orders_cdate vs orders_expdate= datediff(table[orders_cdate],maxx(filter(table,table[account_id] =earlier(table[account_id]) && table[orders_id] 
							<earlier(table[orders_id])),table[orders_expdate]),DAY)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[adeptus2008]

@amitchandak It works great! Thank you!