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

     

     

WimHenderickx's avatar
WimHenderickx
Frequent Visitor
1 year ago

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

 

EmployeStartdateEndDateOrderIndexExpected
15-12-202213-3-202313-3-202311
114-3-202319-3-202313-3-202311
120-3-202310-4-202319-3-202321
111-4-202325-6-202310-4-202331
126-6-202317-1-202425-6-202341
118-1-202431-1-202417-1-202451
11-2-20246-2-202431-1-202461
17-2-20241-3-20246-2-202471
11-7-202431-7-202431-7-202482
11-8-202415-9-202431-7-202482
116-9-202429-10-202415-9-202492
216-3-202320-3-202320-3-202311
216-7-202319-7-202319-7-202311
31-4-20244-4-20244-4-202411
35-4-20249-4-20244-4-202411
Fowmy's avatar
Fowmy
Super User
1 year ago

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's avatar
    WimHenderickx
    Frequent Visitor
    1 year ago

    Yes you are right 

    • 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.