Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Convert dates to numbers based on chronology

Hi!

I have a simple question, but i do not know where to start.

 

I would like to convert a set of dates into numbers based on chronology. E.g. First date = 1, second date = 2

DateResult
13.01.2020 14:34:321
14.01.2020 14:34:322
15.01.2020 14:34:323
16.01.2020 14:34:324

 

PS: i have to use direct query

Thanks!

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    My test environment is Direct Query to connect to the SQL server, and the goal can be achieved by creating measure.

    Rank = RANKX(ALL(Table_1),CALCULATE(MAX('Table_1'[Date])),,ASC,Dense)

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • negi007's avatar
    negi007
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Anonymous  simple way is to create a column based on the rank function that will give ranking to dates in your table. Dax for that column would be like below

     

    Date_Rank = RANK.EQ(Date_Chornology[Date],Date_Chornology[Date],ASC)

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks for taking the time.

      This does not work for me, as i have to use direct query, and i get an error saying that RANK.EQ is not supported in direct query mode. 



  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    My test environment is Direct Query to connect to the SQL server, and the goal can be achieved by creating measure.

    Rank = RANKX(ALL(Table_1),CALCULATE(MAX('Table_1'[Date])),,ASC,Dense)

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    Sorry to disturb you...


    But did I answer your question ? Use RANKX function instead of RANK.EQ Please mark my reply as solution. Thank you very much.

     

    Best Regards,
    Stephen Tao

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi! I am sorry for not replying. No, unfortunatly i got the same message saying that it does not work in direct query. However could an issue be that i also have hh/mm/ss in the date format?