Forum Discussion

rodrigo09062015's avatar
rodrigo09062015
Frequent Visitor
5 years ago

Create a ranking class

Hello, I would like to create a calculated column that shows the date ranking by customer (cod_cliente field)

 

In the example of the client 5663330000212 you have to show 1 for the day 7/1/2020, 2 for the day 7/2/2020 and 3 for the day 5/7/2020.

 

1 Reply

  • OwenAuger's avatar
    OwenAuger
    Super User
    5 years ago

    Hi rodrigo09062015 

    This is a good article on this subject, which recommends RANKX as a useful function for this sort of calculation:
    SQLBI: How to compute index numbers at top speed 

     

    In your case, your calculated column could be:

    Date Ranking by Customer = 
VAR CurrentCustomer = YourTable[cod_cliente]
RETURN
    RANKX ( 
        FILTER (
            YourTable,
            YourTable[cod_cliente] = CurrentCustomer
        ),
        YourTable[Data],
        ,
        ASC
    )

     Regards,

    Owen