Calculate running date difference sum over the period and group by users using measure

[rishijainy]

_id	client_query.User.sAMAccountName	date_time_utc
5e417a59aa384a81f43e7ea8	Alan	27/1/20 15:00
5e417a5eaa384a81f43e7ea9	Alan	27/1/20 16:00
5e417a82aa384a81f43e7eab	Bella	27/1/20 14:00
5e417ab1aa384a81f43e7eac	Leila	15/1/20 16:00
5e417ad6aa384a81f43e7eae	Egor	16/1/20 17:00
5e417adfaa384a81f43e7eaf	Eugene	29/1/20 8:00
5e417aecaa384a81f43e7eb0	Kalyanne	3/2/20 19:00
5e417b08aa384a81f43e7eb1	Benjamin	3/2/20 20:00
5e417b13aa384a81f43e7eb2	Rishabh	10/2/20 7:00
5e417b1daa384a81f43e7eb3	TmpAdmin	10/2/20 5:00
5e417b2aaa384a81f43e7eb4	Alan	10/2/20 15:00
5e417b31aa384a81f43e7eb5	TmpAdmin	10/2/20 6:00
5e417b38aa384a81f43e7eb6	TmpAdmin	10/2/20 7:00
5e417b46aa384a81f43e7eb7		10/2/20 0:00
5e417b55aa384a81f43e7eb8	Rishabh	10/2/20 9:00
5e417b5faa384a81f43e7eb9		10/2/20 0:00
5e417b7daa384a81f43e7eba	TmpAdmin	10/2/20 8:00
5e417b81aa384a81f43e7ebb	Rishabh	10/2/20 10:00
5e417b85aa384a81f43e7ebc	TmpAdmin	10/2/20 10:00
5e417b9aaa384a81f43e7ebd	TmpAdmin	10/2/20 11:00

here I have to calculate 

Average time between two searches. I have created a time slicer as well on the date table which is linked to the above table .So lets say time period in time slicer is from 27-Jan-2020 to 13-feb-2020( this is period) 

• First of all we have to consider the users who has done more than one search over the period. 
• Alan has done 3 searches so calcualtion will be like (27-Jan-2020 16:00 )-(27-Jan-2020 15:00)= 1hr and (10-feb-2020 15:00)-(27-Jan-2020 16:00)=335 hrs   . So final calculation for Alan will be (1hr + 335 hr )/(number of searches-1): (1+335)/2=168hrs 
• Bella,Eugene,kalyanne,benjamin will not be included because there is only one search over the period. 
• Leila and egor should be ignored as they are out of period. 
• Rishabh has done 3 searches so calcualtion will be like (10-Feb-2020 09:00 )-(10-Feb-2020 07:00)= 2hr and (10-feb-2020 10:00)-(10-Feb-2020 09:00)=1 hrs   . So final calculation for Alan will be (2hr + 1 hr )/(number of searches-1): (2+1)/2=1.5hrs 
• TmpAdmin has done 6 searches so calcualtion will be like (10-Feb-2020 11:00 )-(10-Feb-2020 10:00)= 1hr and (10-feb-2020 10:00)-(10-Feb-2020 08:00)=2 hrs  and (10-feb-2020 08:00)-(10-Feb-2020 07:00)=1 hr and (10-feb-2020 07:00)-(10-Feb-2020 06:00)=1 hr and (10-feb-2020 06:00)-(10-Feb-2020 05:00)=1 hr. So final calculation for Alan will be (1hr + 2 hr +1hr+1hr+1hr)/(number of searches-1): (1+2+1+1+1)/5=1.2hrs 
• The average time between 2 search for all users over the period would be (168+1.5+1.2)/3=56.9 hours 
• Please make sure all the calcualtion should consider that there is a time slicer so everything will be dynamic . I don't think it is possible through calculated column. 

[amitchandak]

You can date diff from the previous date like this in the column. But this will not take care if dates are falling across the range you selected

New columns

time diff = datediff(table[date_time_utc], maxx(filter(table,table[client_query.User.sAMAccountName] = earlier(table[client_query.User.sAMAccountName])
					&& table[date_time_utc ] < earlier(table[date_time_utc])),table[date_time_utc]),minute)


time diff = datediff(table[date_time_utc], minx(filter(table,table[client_query.User.sAMAccountName] = earlier(table[client_query.User.sAMAccountName])
					&& table[date_time_utc ] > earlier(table[date_time_utc])),table[date_time_utc]),minute)

instead of minute, you can use hour

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
Proud to be a Datanaut Connect on Linkedin

[rishijainy]

@amitchandak Thanks for your response. But this solution doesn't fulfill my requirements.

First of all it has to be according to the period in slicer and secondly it has to be measure isntead of calculated column .
and it shouldn't be just the difference between min and max date .

[rishijainy]

@mwegener  , @MFelix , @PaulDBrown  , @VasTg can someone help me out for this calculation