Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 😉
Solved! Go to Solution.
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
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
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
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.
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
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
Thank you!:)
It works perfeclty
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" )
Best Regards!
Dale
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |