Forum Discussion

gbrenner35's avatar
gbrenner35
Frequent Visitor
4 years ago
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 Sales...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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.