Ranking query

[Daretoexplore]

Hi everybody

I have a dataset such as the following:

Customer ID.      TransactionDate(UK format)

CUST001.           12/2/24

CUST001.           13/2/24

CUST001.           14/2/24

CUST002.           12/2/24

CUST002.           13/2/24

CUST002.           14/2/24

I'm trying to write DAX or write some M Code to Rank each of the transactions per customer in date order.

The solution I am looking for would be a column which reads 

1

2

3

1

2

3

(this is based on my example above)

Many thanks

[AmiraBedh · Accepted solution]

Using DAX : 

TransactionRank = 
RANKX(
    FILTER(
        'YourTableName',
        'YourTableName'[Customer ID] = EARLIER('YourTableName'[Customer ID])
    ),
    'YourTableName'[TransactionDate],
    ,
    ASC,
    Dense
)

Using M :

1. Go to "Home" > "Edit Queries" to open the Power Query Editor.
2. Select the table you want to modify.
3. Go to "Add Column" tab > "Index Column" > "Custom Index".
4. Start the index from 1 and increment by 1.
5. Before applying the custom index, you might want to ensure your rows are sorted by "Customer ID" and then by "TransactionDate". You can do this by selecting the "Sort Ascending" option on the "Customer ID" column and then doing the same for the "TransactionDate" column.
6. Use the "Group By" feature to group by "Customer ID", and within each group, sort by "TransactionDate" and then add the index column. This would require some advanced M code manipulation.

= Table.Group(
    YourTableName, 
    ["Customer ID"], 
    {
        "Data", 
        each Table.AddIndexColumn(
            Table.Sort(_, {"TransactionDate", Ascending}), 
            "TransactionRank", 
            1, 
            1, 
            Int64.Type
        ), 
        type table
    }
)

[Daretoexplore]

Thank you so much

[AmiraBedh]

Welcome ! Good luck with M it is a little bit tricky sometime 😄