Ranking of individual customer orders by date

[Anonymous]

Hi All,

I have been trying all day today and I havent been succesful so I was hoping you can help me.

I need to get some customer behaviour insights therefore,I am trying to rank the individual customer orders based on the date of a purchase.

what i want to see looks like this

ORDER ID Customer ID  DATE   Order Ranking
6890         17878         15/03/2021             1
9755         19945        16/03/2021              1
4578         17878         17/03/2021             2
6765         17653         18/03/2021             1
6798        17653          19/03/2021             1
5632         17878          20/03/2021            3

Any help will be very much appreciated.

Thank you

Jenny

[Jihwan_Kim]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below.

It is for creating a measure.

Order Ranking Measure =
VAR currentcustomer =
MAX ( 'Table'[Customer ID] )
RETURN
IF (
ISFILTERED ( 'Table'[Order ID] ),
RANKX (
FILTER ( ALL ( 'Table' ), 'Table'[Customer ID] = currentcustomer ),
CALCULATE ( MAX ( 'Table'[Date] ) ),
,
ASC
)
)

https://www.dropbox.com/s/vk8cu96keio45ql/jenmal.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

Hi I am a bit confused, why did you choose the Max formula on the custonmer ID?

Also what if the customer ID was letters instead of numbers?

Thank you 🙂

[v-xulin-mstf · Accepted solution]

Hi @Anonymous

In this case,  MAX ( 'Table'[Customer ID] ) returns the value of the current row.

If you still have some question, please don't hesitate to let me known.‌‌

😉

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[ryan_mayu]

@Anonymous 

I am not sure why the fifth record is 1. I think the ranking should be 2 .

maybe you can try to create a column

Column = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Customer ID]=EARLIER('Table'[Customer ID])&&'Table'[DATE ]<=EARLIER('Table'[DATE ])))

 pls see the attachment below