Forum Discussion

WimHenderickx's avatar
WimHenderickx
Frequent Visitor
1 year ago
Solved

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...
Need Help
  • Fowmy's avatar
    Fowmy
    1 year ago

    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

     

     

nilendraFabric's avatar
nilendraFabric
Super User
1 year ago

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)
)
)
)