need help identifying customers first, 2nd and so on orders

[Anonymous]

Hi,

i have a data set which contains customer orders and im trying to find a way to identify the customers purchases from their 1st order to their latest but i cant find a solution to the problem and i was wondering if you could help me.

Our data set contains customer id, order id, article id and order date among other things

i used to solution from Solved: Customer 1st vs 2nd order behaviour ( excluding ze... - Microsoft Power BI Community  as in i used the formula

= RANKX(FILTER(Fact_table,Fact_table[business_partner_code]=EARLIER(Fact_table[business_partner_code])),Fact_table[demand_date_id],,ASC)

to see which orders were their first ones but when it comes to the second and third order it adds on to that depending on how much was in the first order. 

as an example here is a customer who bought 4 items his first purchase and in his 2nd purchase he bought 6 items.

is there a solution so the second order gets a value of 2 and so on?

[arrowav36]

Hi,

Its showing this error while I am running this query can you please help me to resolve this?

Error : EARLIER/EARLIEST refers to an earlier row context which doesn't exist.

I am trying to find the customers 1st, 2nd, 3rd and so on.. purchases, in front of each order I wanted to write that which number'th of order it is for that particular customer.

Please help me with the solution.

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

Try "Dense" in Rank function. I update your code.

New calculated column:

New Rank = 
RANKX(FILTER(Fact_table,Fact_table[business_partner_code]=EARLIER(Fact_table[business_partner_code])),Fact_table[demand_date_id],,ASC,Dense)

Result is as below.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[arrowav36]

Hi,

Its showing this error while I am running this query can you please help me to resolve this?

Error : EARLIER/EARLIEST refers to an earlier row context which doesn't exist.

I am trying to find the customers 1st, 2nd, 3rd and so on.. purchases, in front of each order I wanted to write that which number'th of order it is for that particular customer.

Please help me with the solution.