cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
fjellfisk Frequent Visitor
Frequent Visitor

DAX and dates and rank

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)

 

Skjermbilde.PNG

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: DAX and dates and rank

@fjellfisk,

 

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
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: DAX and dates and rank

@fjellfisk,

 

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
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
fjellfisk Frequent Visitor
Frequent Visitor

Re: DAX and dates and rank

Thanks! Smiley Happy

 

This worked.

 

I also tried

 

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

 

Regards

Terje