Forum Discussion

Post Prodigy

3 years ago

Solved

RANK by DateTime Column

Hi experts!

How is it possible to get a rank column based on a DateTime column?

ValtteriN
3 years ago
Hi,

For duplicates we can modify the last optional parameter of RANKX e.g.

Dense:

Skip:

In addition to this we can add an index column to the table and use that as a secondary ranking:

Dax:

Column =

var _dateR =

RANKX('Table (15)',value('Table (15)'[Column1]),'Table (15)'[Column1],ASC,Dense) return

var _indexR =

RANKX('Table (15)',value('Table (15)'[Index]),'Table (15)'[Index],ASC,Dense) return

_dateR+_indexR

For ranking column add this:

Rank = RANKX('Table (15)',VALUE('Table (15)'[Column]),,DESC)

End result (change DESC to ASC to match your use case):

3 Replies

ValtteriN
Community Champion
3 years ago
Hi,

Here is one way to do this:

Column = RANKX('Calendar',value('Calendar'[Date]),'Calendar'[Date],DESC)

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/
- joshua1990
  Post Prodigy
  3 years ago
  ValtteriN : Thanks a lot! Does this work with duplicates? It seems like not. How can we add this aspect into the function?
  - ValtteriN
    Community Champion
    3 years ago
    Hi,
    
    For duplicates we can modify the last optional parameter of RANKX e.g.
    
    Dense:
    
    Skip:
    
    In addition to this we can add an index column to the table and use that as a secondary ranking:
    
    Dax:
    
    Column =
    
    var _dateR =
    
    RANKX('Table (15)',value('Table (15)'[Column1]),'Table (15)'[Column1],ASC,Dense) return
    
    var _indexR =
    
    RANKX('Table (15)',value('Table (15)'[Index]),'Table (15)'[Index],ASC,Dense) return
    
    _dateR+_indexR
    
    For ranking column add this:
    
    Rank = RANKX('Table (15)',VALUE('Table (15)'[Column]),,DESC)
    
    End result (change DESC to ASC to match your use case):