Average Start Time across a Number of Days

[mjholland]

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

[mjholland]

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?

[OwenAuger · Accepted 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:

https://www.dropbox.com/s/05iwbfn5142fggu/Average%20start%20time%20across%20a%20number%20of%20days.p...

Hope that helps,

Owen 🙂

[jwademcg]

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.

[mjholland]

Perfect, it works a treat. Thanks for your help!