Forum Discussion
Time Difference between Rows Grouped by Location, Averaged over Last 7 Days
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
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)
4 Replies
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)
Thoughts?
Geofence is both the name of the table and the "Site" column.
Enter is the name of the date time column you suggested.
Create as new column. Seems like you are creating like new a new measure
