Time Difference between Rows Grouped by Location, Averaged over Last 7 Days

[WFKoth]

I'm brand new to BI so please be patient.

What I would like to achieve is a rolling average of the time between arrivals of technicians to a location over the previous 7 days.

EX: This is the only way i could think to illlustrate this. Not my actual data set. The number in green is what I would like to have on a dashboard.

Geofence	Avg Day 1	Avg Day 2	Avg Day 3	Avg Day 4	Avg Day 5	Avg Day 6	Avg Day 7	Avg Wk
Site 1	8	9	7	6	8	5	6	7
Site 2	5	6	4	7	7	3	4	5.14
Site 3	6	10	5	7	8	9	4	7
							Region Avg.	6.38

Here is the actual data:

Geofence	Vehicle	Driver	Date	Entry Time
Site 1	U.47	…	2/23/2020	4:09:00 PM
Site 1	U.40	…	2/23/2020	6:13:00 PM
Site 2	U.38	…	2/23/2020	5:06:00 PM
Site 2	M.06	…	2/23/2020	6:46:00 PM
Site 3	U.38	…	2/23/2020	9:52:00 PM
Site 1	S.22	…	2/24/2020	3:34:00 PM
Site 2	U.28	…	2/24/2020	1:13:00 PM

As you can see I need to subract the Entry Time of each row from its successor but only if they are at the same site. When I export this data from the fleet mgmt. software it groups it by site and places the rows in chronologic order. However, I pull the data every 2 days or so ( hopefully everyday soon ), so site information comes in chunks of 1-3 rows at a time.

Thanks

[amitchandak · Accepted solution]

Assume you have date time entry or create by merging date nad time

time diff = datediff(table[Datetime], maxx(filter(table,table[geofencse] = earlier(table[geofencse]) && table[Datetime]<earlier(table[Datetime])),table[Datetime]),Hour)

[WFKoth]

Thoughts?

Geofence is both the name of the table and the "Site" column.

Enter is the name of the date time column you suggested.

[amitchandak]

Create as new column. Seems like you are creating like new a new measure

[WFKoth]

 Thank you for being so patient. I am clueless here.