cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
eforb1
New Member

Average date/time for event when event times fall within 2 hours of each other

Hi,

 

I'd like the average date/time for events that fall within 2 hours of eachother, when the identification number and calendar dates are the same. For example if an event occured on 25/11 at 4.12pm and 25/11 4.07pm for the same Identification number, I'd like the average column to display 25/11 4.09pm.

 

I'd like to create the following three columns:

 

(1) Average time column under the following conditions:

  1. Identification number is the same
  2. Same date
  3. Times are within a 2 hour period of eachother (on the same date). If times are  more than 2 hours apart, don't average, leave the average cell blank. 
  4. For single-occurence events within a 2 hour period, display that event's date/time in the average column

 

(2) Flagging times that are more than 2 hours apart, but less than 4 hours apart (as 'yes' or blank when not applicable)

 

(3) Flagging times that are more than 2 hours apart, but less than 8 hours apart (and are not between 2 and 4 hours apart) (as 'yes' or blank when not applicable)

 

Please see the layout I'd like below, with notes for what I'm looking for in each column:

Identification numberDate/Time of eventAverage date/time of eventTime elapsed >2<4 hoursTime elapsed >2<8 hours
125/11/2019 16:12:13   
125/11/2019 16:07:35i.e. average of this date/time and previous cell  
121/11/2019 08:25:09   
121/11/2019 08:23:52i.e. average of this date/time and previous cell  
120/11/2019 18:23:31   
120/11/2019 18:20:57i.e. average of this date/time and previous cell  
218/11/2019 18:42:17   
218/11/2019 18:38:04i.e. average of this date/time and previous cell  
218/11/2019 08:27:13   
218/11/2019 08:26:19   
218/11/2019 08:25:46i.e. average of this date/time and 2 previous cells  
208/11/2019 07:54:16Remains the same, as no other time within 2 hours to average with  
310/12/2018 17:38:17Not averaged, as next time is greater than 2 hoursFlagged here ("Yes") 
310/12/2018 21:21:25Not averaged, as earlier time is greater than 2 hoursFlagged here ("Yes") 

 

Many thanks in advance!

3 REPLIES 3
daxer-almighty
Solution Sage
Solution Sage

Good advice to you: Do not calculate this in DAX. Use Power Query... unless you want to suffer. Trust me, this is very good advice which you should follow.

eforb1
New Member

Thanks so much for your response and for your help @amitchandak. Unfortunately the average doesn't work, as it needs to take into account the number of events in that period of being within 2 hours of eachother - which isn't always 2 events.

 

I'm wondering if there's a way to group together times within 2 hours of eachother and create an average of the group? 

 

I'm new to Power BI, but was able to find previous forums to determine time elapsed:

 

Time Elapsed since last event (Hours) = DATEDIFF([Previous Date],[Date],HOUR)
 
And cumulative time elapsed:
 
Time elapsed cumulative (hours) =
var _currTime = ('Duplicate cleaning'[Date])
var _calc = CALCULATE(SUM('Duplicate cleaning'[Time elapsed (hours)]),ALLEXCEPT( 'Duplicate cleaning', 'Duplicate cleaning'[Identification number]),'Duplicate cleaning'[Date]<=_currTime)

return _calc
 
I thought this might be useful to filter the times to be included in an average calculation:
 
averaging attempt 1 =

CALCULATE (
AVERAGE('Duplicate cleaning'[Date]) ,
FILTER (
'Duplicate cleaning',
EARLIER ( 'Duplicate cleaning'[Date] ) = [Date]
&& EARLIER ( 'Duplicate cleaning'[Identification number] ) = [Identification number]
&& ('Duplicate cleaning'[Time elapsed (hours)] <=2)
&& EARLIER( 'Duplicate cleaning'[Time elapsed cumulative (hours)] ) = 'Duplicate cleaning'[Time elapsed cumulative (hours)]))
 
averaging attempt 2 = CALCULATE (
AVERAGE('Duplicate cleaning'[Date]),
FILTER (
'Duplicate cleaning',
EARLIER ( 'Duplicate cleaning'[Date] ) = [Date]
&& EARLIER ( 'Duplicate cleaning'[Identification number] ) = [Identification number]
&& ('Duplicate cleaning'[Time Elapsed since last event (Hours)] <=2)
&& ('Duplicate cleaning'[Time Elapsed since last event (Hours)] >0)))
 
However, this hasn't worked either. I've indicated in the screenshot what I'd like to be included in each average event time.
 
Another approach I had thought of was calculating the min and max event times within the 2 hour period of eachother, then using the number of rows between these min and max times to find the average. I'm not sure if this is possible, as this is my first shot at Power BI!
 
Thanks again for your help.
 
Capture.PNG
 
 

 

amitchandak
Super User IV
Super User IV

@eforb1 , Try a new column like

 

new column =
var _1 = maxx(filter(Table1, Table1[Datetime] < earlier(Table1[Datetime]) && Table1[ID] =earlier(Table1[ID]) ), [Datetime])
var _2 = datediff(_1,[Datetime],hour)
return
if(_2<2, [Datetime]+ ([Datetime] - _1)/2, blank() )



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors