Forum Discussion
Index
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 |
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 __ResultIndex = VAR __Emp = Table03[Employe ] VAR __StartDate = Table03[StartDate] VAR __Result = CALCULATE ( SUM ( Table03[Order] ), Table03[Employe ] = __Emp, Table03[StartDate] <= __StartDate, REMOVEFILTERS ( table03 ) ) RETURN __Result
9 Replies
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)
)
)
)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:Thanks for the formula - but i have some problems - see below the result with these formula's
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.
