cancel
Showing results for
Did you mean:
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 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

1 ACCEPTED SOLUTION

Accepted Solutions
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

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

Proud to be a Datanaut!

4 REPLIES 4
Highlighted
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?

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

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

Proud to be a Datanaut!

Regular Visitor

## Re: Average Start Time across a Number of Days

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

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.

Announcements

#### Community Highlights

Find out what's new in the Power BI Community!

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 105 members 1,509 guests
Recent signins: