topic Re: Ranking Orders by date and customer for product X in DAX Commands and Tips

Ranking Orders by date and customer for product X

akfir — Sun, 08 Oct 2023 08:07:22 GMT

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!

Re: Ranking Orders by date and customer for product X

_elbpower — Sun, 08 Oct 2023 12:11:09 GMT

What if you just take the day of year?

Re: Ranking Orders by date and customer for product X

akfir — Sun, 08 Oct 2023 12:13:51 GMT

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.

Re: Ranking Orders by date and customer for product X

_elbpower — Sun, 08 Oct 2023 12:19:51 GMT

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.

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

Re: Ranking Orders by date and customer for product X

ThxAlot — Sun, 08 Oct 2023 18:48:26 GMT