RANKX with FILTER, EARLIER in a Group of Data

[Limerick]

Hello together, 

im trying to create a RANKX over a few orders that are sorted by Date. 
The Raw Data looks like this:

Customer	Order-Number	Date
A	1a2b3c	01.01.1999
A	1a2b3c	01.01.2000
A	2a2b3c	01.01.2000
A	3a2b3c	01.01.2000
B	1a2b3c	01.01.2000
B	2a2b3c	01.01.2001
B	3a2b3c	01.01.2001
B	3a2b3c	01.01.2001

The result i need looks like this:

Customer	Order-Number	Date	RANK
A	1a2b3c	01.01.1999	1
A	1a2b3c	01.01.1999	1
A	2a2b3c	01.01.2000	2
A	3a2b3c	01.01.2000	3
B	1a2b3c	01.01.2000	1
B	2a2b3c	01.01.2001	2
B	3a2b3c	01.01.2001	3
B	3a2b3c	01.01.2001	3

The ranking starts again for every customer and is depending on the order-number in combination with the date.

I have written this code: 

Ranking = RANKX(

FILTER(ALL('Table'),
'Table'[Customer] = EARLIER('Table'[Customer]) ,

'Table'[Date],,ASC,Dense)

The Result looks like this:

Customer	Order-Number	Date	RANK
A	1a2b3c	01.01.1999	1
A	1a2b3c	01.01.1999	1
A	2a2b3c	01.01.2000	2
A	3a2b3c	01.01.2000	2
B	1a2b3c	01.01.2000	1
B	2a2b3c	01.01.2001	2
B	3a2b3c	01.01.2001	2
B	3a2b3c	01.01.2001	2

So its pretty close to the wished result.
It is Ranking for each Customer by the Date. 
But now i need to rank it over the Date and the Order-Number.

So i tried this:

Ranking = RANKX(

FILTER(ALL('Table'),
'Table'[Customer] = EARLIER('Table'[Customer]
&& 'Table'[Order-Number] = 'Table'[Order-Number]) ,

'Table'[Date],,ASC,Dense)

But unfortunately it doesnt have any effect on the result - its still looking like the last table above.

For my understanding, the Filter is grapping with EARLIER the first Customer and is continuing as long as the Customer are identical. I also would need that for the Order-Number, so I try to say "do it for the Order-Number that are equal".
I cant use the EARLIER on the Order-Number, because then the RANKX is ranking every row with 1.

The table i need should look like this:

Customer	Order-Number	Date	RANK
A	1a2b3c	01.01.1999	1
A	1a2b3c	01.01.1999	1
A	2a2b3c	01.01.2000	2
A	3a2b3c	01.01.2000	3
B	1a2b3c	01.01.2000	1
B	2a2b3c	01.01.2001	2
B	3a2b3c	01.01.2001	3
B	3a2b3c	01.01.2001	3

Unfortunately I also cant Rank over the Order-Number, because its a randomly generated number.

I hope you guys can help me out 🙂 

Ty in advance!

[mahoneypat · Accepted solution]

Here is a column expression that shows one way to do it.

Rank =
VAR thiscustomer = Orders[Customer]
VAR thisorder = Orders[Order-Number]
VAR thisdate = Orders[Date]
VAR stringtorank =
    FORMAT ( thisdate, "YYYYMMDD" ) & thisorder
VAR thiscustomertable =
    FILTER ( Orders, Orders[Customer] = thiscustomer )
VAR withstringstorank =
    ADDCOLUMNS (
        thiscustomertable,
        "cStringtoRank", FORMAT ( Orders[Date], "YYYYMMDD" ) & Orders[Order-Number]
    )
VAR result =
    RANKX ( withstringstorank, [cStringtoRank], stringtorank, aSC )
RETURN
    result

Pat

[Limerick]

Thank you, this is perfekt!
Just had to add DENSE behinde the ASC but nice solution 🙂

[amitchandak]

@Limerick , Try like

Ranking = RANKX(
FILTER(ALL('Table'),
'Table'[Customer] = EARLIER('Table'[Customer]) ) ,
'Table'[Order-Number],,ASC,Dense)

[Limerick]

Hello @amitchandak ,
thank you for your answer.
Unfortunately, as I mentioned before, I cant Rank by the Order-Number itself.
It is important that the order goes by the date.
Best regards 🙂