Forum Discussion

hassanh2's avatar
hassanh2
Helper I
2 years ago
Solved

Rank Customers by Order Date

Hello,

 

Can someone please help with the following - I need to Rank Customer ID by SalesDate Desc.
The Fact table I have has more than 10K customers and each has multiple sales records in the same day. Same day sales shall be ranked the same rank. 

 

Need Help
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi hassanh2 
    You can add calculated column with DAX Formula:

    RankDate =
    RANKX(
        FILTER(
            'table',
            'table'[Customer] = EARLIER('table'[Customer])
        ),
        'table'[Date],
        ,
        DESC
    )
    The sample file is Linked here 
     

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

  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi hassanh2 
    Did you download my linked file?
    I checked it again now, and the results are correct...

3 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    2 years ago

    Hi hassanh2 
    You can add calculated column with DAX Formula:

    RankDate =
    RANKX(
        FILTER(
            'table',
            'table'[Customer] = EARLIER('table'[Customer])
        ),
        'table'[Date],
        ,
        DESC
    )
    The sample file is Linked here 
     

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

    • Ritaf1983's avatar
      Ritaf1983
      Super User
      2 years ago

      Hi hassanh2 
      Did you download my linked file?
      I checked it again now, and the results are correct...

  • hassanh2's avatar
    hassanh2
    Helper I
    2 years ago

    Hi Ritaf1983 
    Thank you for your support, but unfortunately the DAX you sent didnt return the result what I was looking for. In some cases the rank started from 2, also sometimes the rank was not consistent 1,2,4,7,...etc. I tried by adding Dense to your DAX but still didn't solve the issue.