Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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)
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
Thank you for being so patient. I am clueless here.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |