cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mjholland Regular Visitor
Regular Visitor

Average Start Time across a Number of Days

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 IDStore IDVisitDateStart Time Staff IDVisitDateMin of Start Time
Staff A50024004/07/201608:25:00 Staff A04/07/201608:25:00
Staff A36151704/07/201609:55:00 Staff A05/07/201608:30:00
Staff A10048504/07/201611:10:00 Staff A06/07/201608:45:00
Staff A50051504/07/201612:12:00 Staff A07/07/201608:30:00
Staff A10124004/07/201615:50:00    
Staff A10049505/07/201608:30:00 Staff IDAverage of Min of Start Time 
Staff A10030005/07/201610:37:00 Staff A08:32:30 
Staff A10268505/07/201612:06:00    
Staff A10046005/07/201613:38:00    
Staff A50036505/07/201614:33:00    
Staff A20369006/07/201608:45:00    
Staff A20144106/07/201610:27:00    
Staff A20095506/07/201611:25:00    
Staff A20362006/07/201612:44:00    
Staff A20350506/07/201614:10:00    
Staff A10047506/07/201615:52:00    
Staff A20374007/07/201608:30:00    
Staff A50031507/07/201611:45:00    
Staff A39854307/07/201613:30:00    
Staff A10050507/07/201614:55:00    
Staff A50025007/07/201616: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

1 ACCEPTED SOLUTION

Accepted Solutions
OwenAuger Super Contributor
Super Contributor

Re: Average Start Time across a Number of Days

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 Smiley Happy



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

4 REPLIES 4
mjholland Regular Visitor
Regular Visitor

Re: Average Start Time across a Number of Days

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 Super Contributor
Super Contributor

Re: Average Start Time across a Number of Days

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 Smiley Happy



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Highlighted
mjholland Regular Visitor
Regular Visitor

Re: Average Start Time across a Number of Days

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

jwademcg Frequent Visitor
Frequent Visitor

Re: Average Start Time across a Number of Days

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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 45 members 784 guests
Please welcome our newest community members: