cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jajecko Frequent Visitor
Frequent Visitor

Specifying the order type (new/returning)

Hi, 

I'm trying to specify if the order is placed by a new or returning client.

tables which I'm using are:

calendar
sales
customers
Products

 

New client's order is when he didn't place any orders in the last 365 days starting from the order issue date.

I know to how to specify new or returning clients but I'm not sure how to handle specific orders.
I would appreciate if someone could help me finding a solution 😉

3 ACCEPTED SOLUTIONS

Accepted Solutions

Re: Specifying the order type (new/returning)

Hi,

 

I created this on Contoso, seems to work in a reasonable amount of time (a bunch of seconds against 12M rows):

 

OrderType = 
VAR CurrentCustomer = Sales[CustomerKey] 
VAR CurrentDate = Sales[Order Date]
RETURN
IF ( 
    MAXX ( 
        FILTER ( 
            Sales, 
            AND ( 
                Sales[CustomerKey] = CurrentCustomer,
                Sales[Order Date] < CurrentDate
            ) 
        ), 
        Sales[Order Date] 
    ) >= CurrentDate - 365,
    "Returning",
    "New"
) 

 

Keep in mind, new and returning customers is one of my favourite calculations, because it is extremely easy to author it in a different way (which looks more reasonable, by the way), and have serious performance issues. This second version (indeed, it was my first one) takes forever (it is running since 10 minutes on 12M rows and I still don't have the calculation ready):

 

OrderType = 
VAR CurrentDate = Sales[Order Date]
RETURN
IF ( 
    CALCULATE( 
        COUNTROWS ( Sales ),
        ALLEXCEPT( Sales, Sales[CustomerKey] ),
        Sales[Order Date] >= CurrentDate - 365,
        Sales[OnlineSalesKey] < CurrentDate
    ) > 0,
    "Returning",
    "New"
)

 

So, I put this last calculation here for educational purposes only, don't use this second version and go for the previous one, which works much better. CALCULATE is a good function but, in this very specific case, produces an ugly execution plan

 

Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

Alberto Ferrari - SQLBI

View solution in original post

jajecko Frequent Visitor
Frequent Visitor

Re: Specifying the order type (new/returning)

Hi,

Thank you very much for your Help.

Is there any chance that you could provide me a sample .pbix file?

I'm facing some issues with implementing this formula into my project and it would be very helpfull to see the sample file. 

 

Thanks in advance

View solution in original post

Re: Specifying the order type (new/returning)

Sure. From here you can download the Contoso database that I use for most of my tests. It does not contain the calculated column (so I can share the same file again and again), but you can add the column to the model to perform your tests.

 

https://www.dropbox.com/s/pfurwpnsu00hmvn/Contoso.pbix?dl=0

 

Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

Alberto Ferrari - SQLBI

View solution in original post

8 REPLIES 8
v-jiascu-msft Super Contributor
Super Contributor

Re: Specifying the order type (new/returning)

Hi @jajecko,

 

Can you share the .pbix file please? Maybe a measure like this could help:

OrderType =
VAR OrderCustomer =
    MIN ( Sales[CustomerKey] )
VAR orderDate =
    MIN ( 'Sales'[Order Date] ) - 1
RETURN
    IF (
        CALCULATE (
            DISTINCTCOUNT ( 'Sales'[Order Number] ),
            FILTER (
                ALL ( Sales ),
                Sales[Order Date] IN DATESINPERIOD ( 'Date'[Date], orderDate, -365, DAY )
                    && Sales[CustomerKey] = OrderCustomer
            )
        )
            > 0,
        "Returning Client",
        "New"
    )

Specifying the order type.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

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

Re: Specifying the order type (new/returning)

Hi,

 

I created this on Contoso, seems to work in a reasonable amount of time (a bunch of seconds against 12M rows):

 

OrderType = 
VAR CurrentCustomer = Sales[CustomerKey] 
VAR CurrentDate = Sales[Order Date]
RETURN
IF ( 
    MAXX ( 
        FILTER ( 
            Sales, 
            AND ( 
                Sales[CustomerKey] = CurrentCustomer,
                Sales[Order Date] < CurrentDate
            ) 
        ), 
        Sales[Order Date] 
    ) >= CurrentDate - 365,
    "Returning",
    "New"
) 

 

Keep in mind, new and returning customers is one of my favourite calculations, because it is extremely easy to author it in a different way (which looks more reasonable, by the way), and have serious performance issues. This second version (indeed, it was my first one) takes forever (it is running since 10 minutes on 12M rows and I still don't have the calculation ready):

 

OrderType = 
VAR CurrentDate = Sales[Order Date]
RETURN
IF ( 
    CALCULATE( 
        COUNTROWS ( Sales ),
        ALLEXCEPT( Sales, Sales[CustomerKey] ),
        Sales[Order Date] >= CurrentDate - 365,
        Sales[OnlineSalesKey] < CurrentDate
    ) > 0,
    "Returning",
    "New"
)

 

So, I put this last calculation here for educational purposes only, don't use this second version and go for the previous one, which works much better. CALCULATE is a good function but, in this very specific case, produces an ugly execution plan

 

Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

Alberto Ferrari - SQLBI

View solution in original post

jajecko Frequent Visitor
Frequent Visitor

Re: Specifying the order type (new/returning)

Hi,

Thank you very much for your Help.

Is there any chance that you could provide me a sample .pbix file?

I'm facing some issues with implementing this formula into my project and it would be very helpfull to see the sample file. 

 

Thanks in advance

View solution in original post

Re: Specifying the order type (new/returning)

Sure. From here you can download the Contoso database that I use for most of my tests. It does not contain the calculated column (so I can share the same file again and again), but you can add the column to the model to perform your tests.

 

https://www.dropbox.com/s/pfurwpnsu00hmvn/Contoso.pbix?dl=0

 

Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

Alberto Ferrari - SQLBI

View solution in original post

jajecko Frequent Visitor
Frequent Visitor

Re: Specifying the order type (new/returning)

Thank you!:) 
It works perfeclty 

jajecko Frequent Visitor
Frequent Visitor

Re: Specifying the order type (new/returning)

Once again thank you for your help with specyfing the order type. 

 

Right now, after I've managed to specify the OrderType, I'm facing another problem.  

Here is a table where you can see the orders. 

 

screen1.JPG

 

As you can see there is also additional Column which is the "SalesBonus"

 

 

SalesBonus = IF(Sales[OrderType]="Returning";[OrderValue]*0,035;[OrderValue]*0,05)

 

 

For the "New" orders there is a 5% bonus and for "Returning" orders there is 3,5% bonus. 

 

The problem starts when the customer returns the goods from the "New" order. 

"Order returns" are treated as regular orders and they are in the same column which is "orderID"

New order's return cannot be treated as a "New" order so there is only a 3.5% bonus correction 

 

 I'm wondering if I could use the "RelatedOrderID" where the related source order ID is indicated but I'm not sure how to do it;)  

 

I hope I have described the problem understandably and hopefully someone will help me to solve it.

Re: Specifying the order type (new/returning)

Can't you just use a LOOKUPVALUE to retrieve the status of the related order? That should solve your problem.


Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

Alberto Ferrari - SQLBI
Highlighted
jajecko Frequent Visitor
Frequent Visitor

Re: Specifying the order type (new/returning)

LOOKUPVALUE worked perfect. Thank you! 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 107 members 1,382 guests
Please welcome our newest community members: