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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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