There is a set within a group with start and end dates. When the end date and next start date has a gap of only one day, these dates need to be combined.
Continuous Flag = var _1 = DATEDIFF(Sheet1[End Date]+1 , MINX(FILTER(Sheet1, ([Start Date]=EARLIER(Sheet1[End Date])+1 )&& [Group]=EARLIER(Sheet1[Group]) ),[Start Date]),DAY) +DATEDIFF( Sheet1[Start Date] -1, maxx(FILTER(Sheet1, ( [end Date]=EARLIER(Sheet1[Start Date])-1) && [Group]=EARLIER(Sheet1[Group])) ,[End Date]),DAY)
// var _2=[Date Rank] - maxx(FILTER(Sheet1, ( [Date Rank]=EARLIER([Date Rank])-1) ) && [Group]=EARLIER(Sheet1[Group])), [Date Rank])
Var _3 = datediff(maxx(FILTER(Sheet1,[End Date]<EARLIER([Start Date]) && [Group] =EARLIER(Sheet1[Group])),[End Date]),[Start Date].[Date],DAY)
return _1 +if(_3<=1, BLANK(),1)+0
Date
Continuous Date = MAX( MINX(FILTER(Sheet1, ([Start Date]=EARLIER(Sheet1[End Date])+1 ) && [Group]=EARLIER(Sheet1[Group]) ),[Start Date]-1)
,maxx(FILTER(Sheet1, ( [end Date]=EARLIER(Sheet1[Start Date])-1)&& [Group]=EARLIER(Sheet1[Group])) ,[End Date]+1))
New Start Date
New Start Date = Var _1 = maxx(FILTER(Sheet1,[Group] =EARLIER(Sheet1[Group]) && [Continuous Date] <=EARLIER(Sheet1[Continuous Date])&& not(ISBLANK([Continuous Date])) && [Continuous Flag]<>EARLIER([Continuous Flag])),Sheet1[Start Date])
Var _2 = minx(FILTER(Sheet1,[Group] =EARLIER(Sheet1[Group]) && [Continuous Date] <=EARLIER(Sheet1[Continuous Date]) && [Continuous Flag]=EARLIER([Continuous Flag]) && not(ISBLANK([Continuous Flag])) ),Sheet1[Start Date])
Var _3 =SWITCH(True(),
[Continuous Flag]=1,[Start Date],
(ISBLANK(_1) && [Continuous Flag]=0),_2,
not(ISBLANK(_1)),_1,
[Start Date])
return if(ISBLANK(_3),[Start Date],_3)
New End Date
New End Date = maxx(FILTER(Sheet1,[Group]=EARLIER([Group]) && [New Start Date]=EARLIER([New Start Date])),[End Date])
eyJrIjoiNmU5MGY0OGEtNTQ5Yy00ZmM2LTk2YjAtZDRhYWMwYmE5NjRhIiwidCI6ImVhOGJkMWZkLWFjMzQtNGFlMi1iNDIxLTZjZmEyZmNmZjI0MyJ9
