Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I'm trying to work out the average starting time for each member of staff across the days they've worked. Each member of staff visits an number of retailers each day and their start and end time is recorded for each. The number of retailers visited each day will vary.
From the retailers visited each day I would need to work out the earliest start time, then work out the average start time across the total days worked for each member of staff - bearing in mind each member of staff may work different days, for holidays, etc.
Here's an example of the data from excel and how I'm working out the average start time for one member of staff:
Staff ID | Store ID | VisitDate | Start Time | Staff ID | VisitDate | Min of Start Time | |
Staff A | 500240 | 04/07/2016 | 08:25:00 | Staff A | 04/07/2016 | 08:25:00 | |
Staff A | 361517 | 04/07/2016 | 09:55:00 | Staff A | 05/07/2016 | 08:30:00 | |
Staff A | 100485 | 04/07/2016 | 11:10:00 | Staff A | 06/07/2016 | 08:45:00 | |
Staff A | 500515 | 04/07/2016 | 12:12:00 | Staff A | 07/07/2016 | 08:30:00 | |
Staff A | 101240 | 04/07/2016 | 15:50:00 | ||||
Staff A | 100495 | 05/07/2016 | 08:30:00 | Staff ID | Average of Min of Start Time | ||
Staff A | 100300 | 05/07/2016 | 10:37:00 | Staff A | 08:32:30 | ||
Staff A | 102685 | 05/07/2016 | 12:06:00 | ||||
Staff A | 100460 | 05/07/2016 | 13:38:00 | ||||
Staff A | 500365 | 05/07/2016 | 14:33:00 | ||||
Staff A | 203690 | 06/07/2016 | 08:45:00 | ||||
Staff A | 201441 | 06/07/2016 | 10:27:00 | ||||
Staff A | 200955 | 06/07/2016 | 11:25:00 | ||||
Staff A | 203620 | 06/07/2016 | 12:44:00 | ||||
Staff A | 203505 | 06/07/2016 | 14:10:00 | ||||
Staff A | 100475 | 06/07/2016 | 15:52:00 | ||||
Staff A | 203740 | 07/07/2016 | 08:30:00 | ||||
Staff A | 500315 | 07/07/2016 | 11:45:00 | ||||
Staff A | 398543 | 07/07/2016 | 13:30:00 | ||||
Staff A | 100505 | 07/07/2016 | 14:55:00 | ||||
Staff A | 500250 | 07/07/2016 | 16:30:00 |
Can anyone help me figure this out? When I added the Start Time field in to a simple table it appeared as a number rather than a time and didn't give me the option to average.
Thanks,
Mark
Solved! Go to Solution.
Hi Mark,
I'll call your main table Data.
First I would ensure you have a Calendar table including at least Date and the required Week column.
Relate Data[VisitDate] to 'Calendar'[Date].
Then this measure will do what you want:
Average Earliest Start Time = AVERAGEX ( VALUES ( 'Calendar'[Date] ), CALCULATE ( MIN ( Data[Start Time] ) ) )
Note - if multiple Staff IDs are present in the filter context, this measure will simply average the daily minimum Start Time across all Staff IDs. You may want to handle this differently.
When I tested this in Power BI Desktop, I had the same formatting issue as you: it insisted on formatting as a decimal rather than as HH:mm:ss.
So I had to change the measure by adding a zero time on the end (if not blank):
Average Earliest Start Time = VAR Result = AVERAGEX ( VALUES ( 'Calendar'[Date] ), CALCULATE ( MIN ( Data[Start Time] ) ) ) RETURN IF ( NOT ( ISBLANK ( Result ) ), Result + TIME ( 0, 0, 0 ) )
Here is a sample pbix file with dummy data based on yours:
Hope that helps,
Owen 🙂
I should also say I intend to have several weeks in the report, so I'll looking to see a view for each staff member by week, which I can filter.
Any suggestions?
Hi Mark,
I'll call your main table Data.
First I would ensure you have a Calendar table including at least Date and the required Week column.
Relate Data[VisitDate] to 'Calendar'[Date].
Then this measure will do what you want:
Average Earliest Start Time = AVERAGEX ( VALUES ( 'Calendar'[Date] ), CALCULATE ( MIN ( Data[Start Time] ) ) )
Note - if multiple Staff IDs are present in the filter context, this measure will simply average the daily minimum Start Time across all Staff IDs. You may want to handle this differently.
When I tested this in Power BI Desktop, I had the same formatting issue as you: it insisted on formatting as a decimal rather than as HH:mm:ss.
So I had to change the measure by adding a zero time on the end (if not blank):
Average Earliest Start Time = VAR Result = AVERAGEX ( VALUES ( 'Calendar'[Date] ), CALCULATE ( MIN ( Data[Start Time] ) ) ) RETURN IF ( NOT ( ISBLANK ( Result ) ), Result + TIME ( 0, 0, 0 ) )
Here is a sample pbix file with dummy data based on yours:
Hope that helps,
Owen 🙂
Now if only we could put time format on the y axis of a graph. Only allow decimal, and then you can put the actual time format in the tooltip, but in excel and SSRS you can put Time on the Y axis in whatever format you want. Or rather you can format the Y-axis in any format you want using a decimal time.
Perfect, it works a treat. Thanks for your help!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |