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
jajecko
Helper I
Helper I

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

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

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

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

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

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.

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

LOOKUPVALUE worked perfect. Thank you! 

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

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

Thank you!:) 
It works perfeclty 

v-jiascu-msft
Employee
Employee

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.

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.