Ranking Order Dates by Customer

[gbrenner35]

Hi,

I am trying to rank my orders, which are unique to my customers, by OrderDate, i.e. SO52682 will have a 1, SO53684 a rank of 2 and SO56488 a rank of 3...etc.

CustomerKey	SalesOrderNumber	SalesOrderLineNumber	OrderDate	Number of Orders by Customer
11142	SO52682	1	8/17/2003	17
	SO53684	1	9/2/2003	17
		2	9/2/2003	17
		3	9/2/2003	17
	SO56488	1	10/22/2003	17
		2	10/22/2003	17
	SO56610	1	10/24/2003	17
	SO60699	1	12/25/2003	17
		2	12/25/2003	17
		3	12/25/2003	17
	SO62036	1	1/14/2004	17
		2	1/14/2004	17
		3	1/14/2004	17
	SO62247	1	1/17/2004	17
		2	1/17/2004	17
	SO63894	1	2/10/2004	17
	SO66713	1	3/23/2004	17
		2	3/23/2004	17
	SO66784	1	3/24/2004	17
	SO68039	1	4/11/2004	17
		2	4/11/2004	17
		3	4/11/2004	17
	SO72296	1	6/6/2004	17
		2	6/6/2004	17
	SO72801	1	6/13/2004	17
		2	6/13/2004	17
		3	6/13/2004	17
	SO72976	1	6/15/2004	17
		2	6/15/2004	17
		3	6/15/2004	17
	SO73185	1	6/18/2004	17
	SO74272	1	7/4/2004	17
		2	7/4/2004	17
		3	7/4/2004	17
	SO74625	1	7/16/2004	17
		2	7/16/2004	17
Grand Total

I tried this calculated column, but it returned rank of 1-35 (skipping numbers in between) instead of 17.

Purchase Date Index

=RANKX(CALCULATETABLE(Sales, ALLEXCEPT(Sales,Sales[CustomerKey], Sales[Number of Orders by Customer])),Sales[OrderDate],,ASC)

ReplyForward

[Anonymous · Accepted solution]

Hi @gbrenner35 ,

1. Go to Power Query to transform the data:

 
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type"," ",null,Replacer.ReplaceValue,{"SalesOrderNumber"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"CustomerKey","SalesOrderNumber"}),
    #"Added Index" = Table.AddIndexColumn(#"Filled Down", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

2. Then use the following formula:

Column = 
var _column= RANKX(FILTER('Table',[CustomerKey]=EARLIER('Table'[CustomerKey]) && [SalesOrderNumber]=EARLIER([SalesOrderNumber])),[OrderDate],,ASC,Dense)
return 
RANKX(FILTER('Table',[CustomerKey]=EARLIER('Table'[CustomerKey]) && [SalesOrderNumber]=EARLIER([SalesOrderNumber])),_column*100+[Index],,ASC,Dense)

Below is the final output:

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