Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
hassanh2
Helper I
Helper I

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. 

hassanh2_0-1695704203652.png

 

1 ACCEPTED SOLUTION
Ritaf1983
Super User
Super User

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.

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

3 REPLIES 3
hassanh2
Helper I
Helper I

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.

Ritaf1983
Super User
Super User

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.

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.