cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
VusalKarimov
Regular Visitor

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

Mh0SR.png

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

1 ACCEPTED SOLUTION

Hi, @VusalKarimov 

 

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

vzhangti_0-1673861416597.png

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])
        )
    )
)

vzhangti_1-1673861443247.png

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.

View solution in original post

6 REPLIES 6
VusalKarimov
Regular Visitor

VusalKarimov_0-1673002889163.png

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

 

Hi, @VusalKarimov 

 

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

vzhangti_0-1673861416597.png

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])
        )
    )
)

vzhangti_1-1673861443247.png

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
Community Support
Community Support

Hi, @VusalKarimov 

 

You can try the following methods.

vzhangti_0-1672995282095.png

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] )
    )
)

vzhangti_1-1672995418643.png

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.

VusalKarimov
Regular Visitor

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

Jo @VusalKarimov ,

 

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
    )

 

Mikelytics_0-1672998083406.png

 

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
    )

Mikelytics_1-1672998142687.png

 

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.

 

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!
Mikelytics
Resident Rockstar
Resident Rockstar

Hi @VusalKarimov 

 

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
)

 

 

Mikelytics_1-1672944435601.png

 

 

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.

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors