Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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, @Anonymous 

 

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
Anonymous
Not applicable

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, @Anonymous 

 

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, @Anonymous 

 

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.

Anonymous
Not applicable

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

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
    )

 

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

 

 

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.