Aggregate rows only if datetime is consecutive

Hi Anonymous ,

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.