topic Re: RANK by DateTime Column in DAX Commands and Tips

RANK by DateTime Column

joshua1990 — Mon, 02 Jan 2023 09:17:53 GMT

Hi experts!

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

ValtteriN — Mon, 02 Jan 2023 09:33:34 GMT

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 — Mon, 02 Jan 2023 10:49:48 GMT

ValtteriN : Thanks a lot! Does this work with duplicates? It seems like not. How can we add this aspect into the function?

ValtteriN — Mon, 02 Jan 2023 13:13:42 GMT

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):