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

[Anonymous]

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 number	Date/Time of event	Average date/time of event	Time elapsed >2<4 hours	Time elapsed >2<8 hours
1	25/11/2019 16:12:13
1	25/11/2019 16:07:35	i.e. average of this date/time and previous cell
1	21/11/2019 08:25:09
1	21/11/2019 08:23:52	i.e. average of this date/time and previous cell
1	20/11/2019 18:23:31
1	20/11/2019 18:20:57	i.e. average of this date/time and previous cell
2	18/11/2019 18:42:17
2	18/11/2019 18:38:04	i.e. average of this date/time and previous cell
2	18/11/2019 08:27:13
2	18/11/2019 08:26:19
2	18/11/2019 08:25:46	i.e. average of this date/time and 2 previous cells
2	08/11/2019 07:54:16	Remains the same, as no other time within 2 hours to average with
3	10/12/2018 17:38:17	Not averaged, as next time is greater than 2 hours	Flagged here ("Yes")
3	10/12/2018 21:21:25	Not averaged, as earlier time is greater than 2 hours	Flagged here ("Yes")

Many thanks in advance!

[daxer-almighty]

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.

[Anonymous]

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.

[amitchandak]

@Anonymous , 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() )