Sequence numbers of orders date by customer id and date of purchase

[Anonymous]

 How I can create calculated column Sequence numbers of orders date by customer id and date of purchase ?

[Anonymous]

 I just want to do this. When one date for ranks one time. For Special Stores and Return skips ranking. 

[v-zhangti · Accepted solution]

Hi, @Anonymous 

You still need to add an index column, because there are 2 rows that are the same date and need to be sorted.

Rank = 
IF ( OR ( [Order type] = "Return", [Store] = "D" ), BLANK (),
    CALCULATE ( COUNT ( 'Table'[Customer Email] ),
        FILTER ( 'Table',
            [Order type] <> "Return" && [Store] <> "D"
                && [Customer Email] = EARLIER ( 'Table'[Customer Email] )
                && [Index]<=EARLIER('Table'[Index])
        )
    )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[v-zhangti]

Hi, @Anonymous 

You can try the following methods.

Column:

Rank =
CALCULATE ( COUNT ( 'Table'[Customer Email] ),
    FILTER ( 'Table',
        [Customer Email] = EARLIER ( 'Table'[Customer Email] )
            && [Date of Purchase] <= EARLIER ( 'Table'[Date of Purchase] )
            && [Index] <= EARLIER ( 'Table'[Index] )
    )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[Anonymous]

Thank you @Mikelytics . But it doesnt work, when one email repaits (2+ times) in one date of purchase

[Mikelytics]

Jo @Anonymous ,

question is, what is your expected result when you have two purchases on one date? I added two new versions where I actually used the RANKX function is one of them what your are looking for?

1. RANKX with Dense

Rank with Rank Function (Dense) = 
var _Email = [Customer Email]
var _DateOfRow = [Date of Purchase]

RETURN

    RANKX(
        FILTER(SampleTableRank,SampleTableRank[Customer Email]=_Email),
        [date of Purchase],,ASC,Dense
    )

2. RANKX with Skip

Rank with Rank Function (Skip) = 
var _Email = [Customer Email]
var _DateOfRow = [Date of Purchase]

RETURN

    RANKX(
        FILTER(SampleTableRank,SampleTableRank[Customer Email]=_Email),
        [date of Purchase],,ASC,Skip
    )

if this does not work please also take a look on the approach provided by @v-zhangti which provides another possible result.

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[Mikelytics]

Hi @Anonymous 

Please try the following

Rank = 
var _Email = [Customer Email]
var _DateOfRow = [Date of Purchase]

RETURN
CALCULATE(
    COUNTROWS(SampleTableRank),
    ALL(SampleTableRank),
    SampleTableRank[Customer Email] = _Email,
    SampleTableRank[Date of Purchase] <= _DateOfRow
)

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.