Ranking

[Anonymous]

Hi all,

I'm trying to rank each customer's purchases chronologically based on their purchase date starting with "1" for each customer's first purchase and then sequencing numercially with each additional purchase (i.e. their 2nd purchase would populate rank as "2", etc.) 

I want the rank to start over from "1" with each individual client. As you can see for below for client, C100456 the formula is working correctly, but for client C100217 it isn't. 

This is my current formula: 

Rank = RANKX(ALLEXCEPT(ep_Sales_Invoice_Line, ep_Sales_Invoice_Line[Customer_Name]), CALCULATE(MAX(ep_Sales_Invoice_Line[INVOICENUMBER])),,ASC, DENSE)-1

Thank you for all your help! 🙂 

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Rank invoicenumber base measure: = 
RANKX (
    FILTER (
        ALL ( ep_Sales_Invoice_Line ),
        ep_Sales_Invoice_Line[Customer_Account_Number]
            = MAX ( ep_Sales_Invoice_Line[Customer_Account_Number] )
    ),
    CALCULATE ( MAX ( ep_Sales_Invoice_Line[INVOICENUMBER] ) ),
    ,
    ASC
)

Rank invoicedate base measure: = 
RANKX (
    FILTER (
        ALL ( ep_Sales_Invoice_Line ),
        ep_Sales_Invoice_Line[Customer_Account_Number]
            = MAX ( ep_Sales_Invoice_Line[Customer_Account_Number] )
    ),
    CALCULATE ( MAX ( ep_Sales_Invoice_Line[Invoice_Date] ) ),
    ,
    ASC
)

Rank measure: =
RANKX (
    FILTER (
        ALL ( ep_Sales_Invoice_Line ),
        ep_Sales_Invoice_Line[Customer_Account_Number]
            = MAX ( ep_Sales_Invoice_Line[Customer_Account_Number] )
    ),
    [Rank invoicedate base measure:] + [Rank invoicenumber base measure:] / 100,
    ,
    ASC
)

[Anonymous]

Thank you so much, this fixed our problem 🙂 

[rodrigosan]

Hello, @Anonymous how are you?
I don't know if my solution can help you.
What I did was create a key in the fSales table with the date in numeric format (yyyymmdd) and with the CustomerKey.
After I duplicated this table, I left only the Data / CustomerKey / Key columns.
I created a group and inserted an index inside the group.
Then I expanded and merged with fSales table based on Key.
Then I disabled the copy load.
I have my example file here at this link: https://easyupload.io/mjew8f

Did I solve your problem?
Please mark as solution so others can find this solution.
https://www.linkedin.com/in/rodrigosanpbi/