cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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
Community Support

Re: DAX and dates and rank

@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
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

2 REPLIES 2
Community Support
Community Support

Re: DAX and dates and rank

@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
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

Anonymous
Not applicable

Re: DAX and dates and rank

Thanks! 🙂

 

This worked.

 

I also tried

 

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

 

Regards

Terje

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors