Group by with gaps in data

Hi  emmabrice ,

Here are the steps you can follow：

1. Create calculated column.

Index = RANKX('Table',[Effective Date],,ASC,Dense)

Column =
var _lastrow=CALCULATE(MAX('Table'[Job Title]),FILTER(ALL('Table'),[Index]=EARLIER('Table'[Index])-1))
return
IF(_lastrow=[Job Title],0,1)

Group = SUMX(FILTER(ALL('Table'),[Index]<=EARLIER('Table'[Index])),[Column])

2. Create calculated table.

Table 2 =
var _summarize=
SUMMARIZE('Table',[PersonID],[Job Title],[Group],"1",MIN('Table'[Effective Date]),"2",MAX('Table'[End Date]))
return
SELECTCOLUMNS(_summarize,"PersonID",[PersonID],"Effective Date",[1],"End Date",[2],"Job Title",[Job Title])

3. Result:

Best Regards,

Liu Yang

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