Reply
Frequent Visitor
Posts: 10
Registered: ‎05-08-2018
Accepted Solution

DAX and dates and rank

[ Edited ]

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

 


Accepted Solutions
Community Support Team
Posts: 3,835
Registered: ‎07-09-2016

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.

View solution in original post


All Replies
Community Support Team
Posts: 3,835
Registered: ‎07-09-2016

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.
Highlighted
Frequent Visitor
Posts: 10
Registered: ‎05-08-2018

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