Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mjholland
Helper II
Helper II

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

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 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

4 REPLIES 4
mjholland
Helper II
Helper II

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:

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

 

Hope that helps,

Owen 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.