Forum Discussion
Solved
Ranking Order Dates by Customer
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 |
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.
1 Reply
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.