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

     

     

Fowmy's avatar
Fowmy
Super User
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

 

 

WimHenderickx's avatar
WimHenderickx
Frequent Visitor
1 year ago

Thanks for all your help.