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 

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

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

 

  • Fowmy's avatar
    Fowmy
    Super User
    1 year ago

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