Ranking Orders by date and customer for product X

[akfir]

Hi masters,
I wish to a add a calculated column of ranking orders by date and customer for a specific Product. Demo data below:

please assist with the correct DAX

thanks!

[ThxAlot]

[_elbpower]

What if you just take the day of year?

[akfir]

didnt understand your suggestion.
i need to figure out what is the customer X product 1st order, 2nd order, 3rd and so on... day of year has nothing to do with that.

[_elbpower · Accepted solution]

Assuming you have a table named Orders with columns OrderID and OrderDate, you can add a calculated column to calculate the day of the year and then use it for sorting:

DayOfYear = YEAR(Orders[OrderDate]) * 1000 + DAYOFYEAR(Orders[OrderDate])

This expression combines the year and the day of the year to create a unique numeric value like 2023001 for January 1st, 2023, and so on
1. Now, you can sort the OrderID based on this calculated column. Go to the "Modeling" tab and select the Orders table.

2. In the "Sort by Column" option, choose the DayOfYear column
3.Finally, make sure to use the DayOfYear column as the sorting column in your visuals where you want to sort by this rank.

 

OR

OrderSequence =
RANKX(
    FILTER(
        Orders,
        Orders[CustomerID] = EARLIER(Orders[CustomerID]) &&
        Orders[ProductID] = EARLIER(Orders[ProductID])
    ),
    Orders[OrderDate]
)