Forum Discussion

StuartSmith's avatar
StuartSmith
Power Participant
2 years ago
Solved

Get 2nd & 3rd Rows in a column.

I have a table similar to the one below...

MasterID AbsenceDate Name AbsenceID 
1 24/11/2023 John Doe 511 
1 27/11/2023 John Doe 512 
1 28/11/2023 John Doe 513 
2 20/11/2023 Jane Doe 518 
2 21/11/2023 Jane Doe 519 
2 22/11/2023 Jane Doe 520 
2 23/11/2023 Jane Doe 521 

with the "AbsenceDate" only being weekdays.

 

I have a calculated column that have various variable in it and I need 2 variables that will get the 2nd & 3rd absence dates for each "Master ID".  I tried... 

VAR AbsenceStartDatePlus1 ='Table: Absence Recording'[2) Absence Start Date]+1
and this simply increments the 1st date, and doesnt select the next row.  How can I get 2 variable to show the 2nd & 3rd dates in the column for each MasterID.
Thanks in advance.
Need Help
  • danextian's avatar
    danextian
    2 years ago

    hI StuartSmith ,

     

    First create a rank by masterid calculated column:

    Absence Date Rank by MasterID = 
IF (
    WEEKDAY ( 'Table'[AbsenceDate], 2 ) <= 5,
    RANKX (
        FILTER (
            ALL ( 'Table' ),
            WEEKDAY ( 'Table'[AbsenceDate], 2 ) <= 5
                && 'Table'[MasterID] = EARLIER ( 'Table'[MasterID] )
        ),
        'Table'[AbsenceDate],
        ,
        asc,
        DENSE
    )
)

    and then to get the 2nd and 3rd rows

     

7 Replies

    • StuartSmith's avatar
      StuartSmith
      Power Participant
      2 years ago

      So I want 2 variables such as 

      VAR 2ndAbsenceDate = ...

      VAR 3rd AbsenceDate = ...

       

      the value stored in "2ndAbsenceDate" for AbsenceID 1 would be 27/11/2023 & AbsenceID 2 would be 21/11/2023 and for "3rdAbsenceDate" for AbsenceID 1 would be 28/11/2023 & AbsenceID 2 would be 22/11/2023.

       

      So simply want 2 varibles to select the 2nd and 3rd rows of each "MasterID"

  • danextian's avatar
    danextian
    Super User
    2 years ago

    hI StuartSmith ,

     

    First create a rank by masterid calculated column:

    Absence Date Rank by MasterID = 
IF (
    WEEKDAY ( 'Table'[AbsenceDate], 2 ) <= 5,
    RANKX (
        FILTER (
            ALL ( 'Table' ),
            WEEKDAY ( 'Table'[AbsenceDate], 2 ) <= 5
                && 'Table'[MasterID] = EARLIER ( 'Table'[MasterID] )
        ),
        'Table'[AbsenceDate],
        ,
        asc,
        DENSE
    )
)

    and then to get the 2nd and 3rd rows