Index

[WimHenderickx]

Looking for formula that creates index as in example. Per employee ID it is checked whether between the notifications e.g. Enddate first line and Startdate 2nd line there is a day difference if there is no difference then the index remains the same if difference >1 day then the index is increased.

Employe	StartDate	EndDate	Index
1	1-1-2025	8-1-2025	1
1	9-1-2025	16-1-2025	1
1	1-2-2025	8-2-2025	2
1	1-3-2025	2-3-2025	3
2	4-2-2025	6-2-2025	1
2	3-3-2025	4-3-2025	2
2	5-3-2025	6-3-2025	2

[Fowmy]

@WimHenderickx 

Create two calculated column to achieve this.
1st Column:

Order = 
VAR __CurrStartDate = Table03[StartDate]
VAR __CurrEndDate = Table03[EndDate]
VAR __PrevEndDate = CALCULATE( MAX(Table03[EndDate]) , Table03[EndDate] < __CurrStartDate  , ALLEXCEPT(Table03, Table03[Employe ]) )
RETURN
   IF(ISBLANK( __PrevEndDate) , __CurrEndDate , IF( INT(__CurrStartDate - __PrevEndDate) = 1 , __PrevEndDate , __CurrEndDate ))

2nd Column:

Index = 
ROWNUMBER( ALL(Table03[Employe ],Table03[Order]) , ORDERBY( Table03[Order] ) , PARTITIONBY( Table03[Employe ] ))

Result:

[WimHenderickx]

Thanks for the formula - but i have some problems - see below the result with these formula's

[Fowmy]

@WimHenderickx 

Let me check it out, paste your sample data in the reply rather than sharing it as image.
include two or three employees as well.

[WimHenderickx]

It goes wrong after the second row when there is no gap between the endDate and startdate

Employe	Startdate	EndDate	Order	Index	Expected
1	5-12-2022	13-3-2023	13-3-2023	1	1
1	14-3-2023	19-3-2023	13-3-2023	1	1
1	20-3-2023	10-4-2023	19-3-2023	2	1
1	11-4-2023	25-6-2023	10-4-2023	3	1
1	26-6-2023	17-1-2024	25-6-2023	4	1
1	18-1-2024	31-1-2024	17-1-2024	5	1
1	1-2-2024	6-2-2024	31-1-2024	6	1
1	7-2-2024	1-3-2024	6-2-2024	7	1
1	1-7-2024	31-7-2024	31-7-2024	8	2
1	1-8-2024	15-9-2024	31-7-2024	8	2
1	16-9-2024	29-10-2024	15-9-2024	9	2
2	16-3-2023	20-3-2023	20-3-2023	1	1
2	16-7-2023	19-7-2023	19-7-2023	1	1
3	1-4-2024	4-4-2024	4-4-2024	1	1
3	5-4-2024	9-4-2024	4-4-2024	1	1

[Fowmy]

@WimHenderickx 

Should this be 1, 2

2	16-3-2023	20-3-2023	20-3-2023	1	1
2	16-7-2023	19-7-2023	19-7-2023	1	1

[WimHenderickx]

Yes you are right 

[Fowmy · Accepted solution]

@WimHenderickx 

Please try now:

Order = 
VAR __CurrStartDate = Table03[StartDate]
VAR __PrevEndDate =
    CALCULATE (
        MAX ( Table03[EndDate] ),
        Table03[EndDate] < __CurrStartDate,
        ALLEXCEPT ( Table03, Table03[Employe ] )
    )
VAR __Result =
    IF (
        ISBLANK ( __PrevEndDate ),
        1,
        IF ( INT ( __CurrStartDate - __PrevEndDate ) <= 1, 0, 1 )
    )
RETURN
    __Result

Index = 
VAR __Emp = Table03[Employe ]
VAR __StartDate = Table03[StartDate]
VAR __Result =
    CALCULATE (
        SUM ( Table03[Order] ),
        Table03[Employe ] = __Emp,
        Table03[StartDate] <= __StartDate,
        REMOVEFILTERS ( table03 )
    )
RETURN
    __Result

[WimHenderickx]

Thanks for all your help.

[nilendraFabric]

@WimHenderickx 

Give it a try

Index =
1 +
CALCULATE(
SUMX(
FILTER(
YourTable,
YourTable[Employee] = EARLIER(YourTable[Employee])
&& YourTable[StartDate] <= EARLIER(YourTable[StartDate])
),
VAR currStart = YourTable[StartDate]
VAR prevStart =
CALCULATE(
MAX(YourTable[StartDate]),
FILTER(
YourTable,
YourTable[Employee] = EARLIER(YourTable[Employee])
&& YourTable[StartDate] < currStart
)
)
VAR prevEnd =
CALCULATE(
MAX(YourTable[EndDate]),
FILTER(
YourTable,
YourTable[Employee] = EARLIER(YourTable[Employee])
&& YourTable[StartDate] = prevStart
)
)
RETURN
IF(
ISBLANK(prevEnd),
0,
IF(currStart - prevEnd > 1, 1, 0)
)
)
)