Aggregate rows only if datetime is consecutive

[ainabk99]

Hello! I am trying to do something with PowerBI and can't find the correct way of doing it.

The idea is the following, I have a table similar to this:

Location	Start DateTime	End DateTime	Type
A	10/10 01:00	10/10 02:00	a
A	10/10 02:00	10/10 03:00	a
A	10/10 03:00	10/10 04:00	a
A	10/10 08:00	10/10 09:00	a
A	10/10 10:00	10/10 11:00	b
A	10/10 11:00	10/10 12:00	b
B	10/10 01:00	10/10 02:00	a
B	10/10 04:00	10/10 05:00	b
B	10/10 05:00	10/10 06:00	b
B	10/10 07:00	10/10 08:00	a

All the time durations are of 1h. 

What I want to achieve is to aggregate the rows where datetimes are consecutive (and location and type are the same), and get something like this:

Location	Start DateTime	End DateTime	Type
A	10/10 01:00	10/10 04:00	a
A	10/10 08:00	10/10 09:00	a
A	10/10 10:00	10/10 12:00	b
B	10/10 01:00	10/10 02:00	a
B	10/10 04:00	10/10 06:00	b
B	10/10 07:00	10/10 08:00	a

Any idea? 

Thank you!!

[v-eqin-msft · Accepted solution]

Hi @ainabk99 ,

Please follow these steps.

1. Add a Index column

2. Create measures:

Rank = RANKX (FILTER(ALL ('Table'), [Location] = MAX ('Table'[Location]) && [Type]=MAX('Table'[Type])),CALCULATE (MAX(('Table'[Start DateTime]))),,ASC)


Flag = 
var _next= CALCULATE(MAX('Table'[Start DateTime]),FILTER(ALL('Table'),[Location]=MAX('Table'[Location]) && [Type]=MAX('Table'[Type]) && [Rank]=MAXX('Table',[Rank])+1))
var _pre= CALCULATE(MAX('Table'[Start DateTime]),FILTER(ALL('Table'),[Location]=MAX('Table'[Location]) && [Type]=MAX('Table'[Type]) && [Rank]=MAXX('Table',[Rank])-1))
var _diff1=DATEDIFF(_pre, MAX('Table'[Start DateTime]),HOUR)
var _diff2=DATEDIFF(MAX('Table'[Start DateTime]),_next,HOUR)
return IF(_diff2=1 || _diff1=1 ,1,0)


Start = IF([Flag]=1, MINX(FILTER(ALL('Table'),[Location]=MAX('Table'[Location]) && [Type]=MAX('Table'[Type]) &&[Flag]=1),[Start DateTime]),MAX('Table'[Start DateTime]))


End = IF([Flag]=1, MAXX(FILTER(ALL('Table'),[Location]=MAX('Table'[Location]) && [Type]=MAX('Table'[Type]) &&[Flag]=1),[End DateTime]),MAX('Table'[End DateTime]))

Output:

3.Then create a measure for visual-level filter to keep distinct rows:

For filter = IF(MAX('Table'[Start DateTime])=[Start],1,0)

Final output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[watkinnc]

Make sure to sort before grouping, then add the GroupKind.Local parameter to the end of your group step.

--Nate