Calculated column for RANK by product and person

[PaulDBrown]

Good morning,

Can someone help me with this? I am trying to create a calculated column which creates a rank based on product by sales person. The rank itself establishes the order by date. In other words, a sales person has a number of products which they sell consecutively: for example, "James" sells product "A" from the 25 May until 1st June, and then moves onto product "F" for a few days and then another product (etc). What I am interested is in establishing the order by date a person has sold each product.

I have suceeded in establishing the total order of products sold using the RANK.Q function with the related date, but I can't seem to find the way to establish this order by person and product.

This is the original table:

Date Min date Product and sales person Product Sales Person Rank by Product min Date   27/05/2019 0:00 27/05/2019 0:00 A James 1   28/05/2019 0:00 27/05/2019 0:00 A James 1   29/05/2019 0:00 27/05/2019 0:00 A James 1   30/05/2019 0:00 27/05/2019 0:00 A James 1   31/05/2019 0:00 27/05/2019 0:00 A James 1   01/06/2019 0:00 27/05/2019 0:00 A James 1   01/06/2019 0:00 01/06/2019 0:00 A Peter 7   02/06/2019 0:00 01/06/2019 0:00 A Peter 7   02/06/2019 0:00 02/06/2019 0:00 F James 13   03/06/2019 0:00 01/06/2019 0:00 A Peter 7   03/06/2019 0:00 02/06/2019 0:00 F James 13   04/06/2019 0:00 01/06/2019 0:00 A Peter 7   04/06/2019 0:00 02/06/2019 0:00 F James 13   05/06/2019 0:00 01/06/2019 0:00 A Peter 7   05/06/2019 0:00 02/06/2019 0:00 F James 13   06/06/2019 0:00 06/06/2019 0:00 A Ana 19   06/06/2019 0:00 01/06/2019 0:00 A Peter 7   06/06/2019 0:00 02/06/2019 0:00 F James 13   07/06/2019 0:00 06/06/2019 0:00 A Ana 19   07/06/2019 0:00 07/06/2019 0:00 B Peter 25   07/06/2019 0:00 02/06/2019 0:00 F James 13   08/06/2019 0:00 06/06/2019 0:00 A Ana 19   08/06/2019 0:00 07/06/2019 0:00 B Peter 25   08/06/2019 0:00 08/06/2019 0:00 C James 31   09/06/2019 0:00 06/06/2019 0:00 A Ana 19   09/06/2019 0:00 07/06/2019 0:00 B Peter 25   09/06/2019 0:00 08/06/2019 0:00 C James 31   10/06/2019 0:00 06/06/2019 0:00 A Ana 19   10/06/2019 0:00 07/06/2019 0:00 B Peter 25   10/06/2019 0:00 08/06/2019 0:00 C James 31   11/06/2019 0:00 06/06/2019 0:00 A Ana 19   11/06/2019 0:00 07/06/2019 0:00 B Peter 25   11/06/2019 0:00 08/06/2019 0:00 C James 31   12/06/2019 0:00 07/06/2019 0:00 B Peter 25   12/06/2019 0:00 08/06/2019 0:00 C James 31   12/06/2019 0:00 12/06/2019 0:00 F Ana 36   13/06/2019 0:00 13/06/2019 0:00 C Peter 42   13/06/2019 0:00 12/06/2019 0:00 F Ana 36   13/06/2019 0:00 13/06/2019 0:00 G James 42   14/06/2019 0:00 13/06/2019 0:00 C Peter 42   14/06/2019 0:00 12/06/2019 0:00 F Ana 36   14/06/2019 0:00 13/06/2019 0:00 G James 42   15/06/2019 0:00 13/06/2019 0:00 C Peter 42   15/06/2019 0:00 12/06/2019 0:00 F Ana 36   15/06/2019 0:00 13/06/2019 0:00 G James 42   16/06/2019 0:00 13/06/2019 0:00 C Peter 42   16/06/2019 0:00 12/06/2019 0:00 F Ana 36   16/06/2019 0:00 13/06/2019 0:00 G James 42   17/06/2019 0:00 13/06/2019 0:00 C Peter 42   17/06/2019 0:00 12/06/2019 0:00 F Ana 36   17/06/2019 0:00 13/06/2019 0:00 G James 42   18/06/2019 0:00 18/06/2019 0:00 C Ana 52

And what I'm trying to get is the final column ("expected ranking")

Thanks!

[Anonymous]

@PaulDBrown 

Column = 
VAR _name = 'Table'[Sales Person]
RETURN RANKX(FILTER('Table','Table'[Sales Person]=_name),'Table'[Min date Product and sales person],,ASC,Dense)

please create a calculated column 

[PaulDBrown]

@Anonymous 

Thanks a million! worked like a charm!

[v-lili6-msft · Accepted solution]

hi @PaulDBrown 

For this case, you could use EARLIER Function in the formula as below:

Column 2 = 
RANKX(FILTER('Table','Table'[Sales Person]=EARLIER('Table'[Sales Person])),'Table'[Min date Product and sales person],,ASC,Dense)

https://docs.microsoft.com/en-us/dax/earlier-function-dax

Regards,

Lin