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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors