Get 2nd & 3rd Rows in a column.

[StuartSmith]

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.

[danextian · Accepted solution]

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

[StuartSmith]

Thanks, that worked.

[StuartSmith]

Let me test and get back to you. Thanks 

[Ahmedx]

what is the expected result? pls write

[StuartSmith]

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"

[Ahmedx]

pls try this

[StuartSmith]

Thanks for taking the time to supply a solution, but I ave used the other solution supplied by the open comtributor as I can user the "Ranking" column for additional formulas.  But I do appreciate your time and effort. 👊