DAX and dates and rank

[Anonymous]

Hi, I have this table. I Also have an external date table. In this case, I have selected the date 30.10.2018. I want the 3 rows in this table to be filtered out when selecting this date, because rowno 1 and Rowno 2 have the same startdate, therefore the row with the latest EndDate within these 2 rows will "Win". The startdate and enddate in the 3rd row isn't between the selected date and will be filtered out as well.

I tried Rank/Earlier on the startdate, But I also need the rank to look into the enddate and see which of the 2 rows have the latest enddate.

I have Scribbled the wanted result in attachment below. 

Anoyone?

I tried this:

RANK=RANKX('Table','table'[startdate] && 'Table'[Enddate],,ASC,dense)

[v-chuncz-msft · Accepted solution]

@Anonymous,

You may try the DAX below.

Column =
RANKX ( Table1, Table1[StartDate],, ASC, SKIP )
    + RANKX (
        FILTER ( Table1, Table1[StartDate] = EARLIER ( Table1[StartDate] ) ),
        Table1[EndDate],
        ,
        ASC,
        SKIP
    )
    - 1

[Anonymous]

Thanks! 🙂

This worked.

I also tried

RANKX('Table';[StartDate]+[Enddate];;ASC;DENSE) This gave the same result in this case. 

Regards

Terje