cancel
Showing results for
Did you mean:
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
Highlighted

## 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
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.

## 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
8 REPLIES 8
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"
)```

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.
Highlighted

## 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
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.

## 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
Frequent Visitor

## Re: Specifying the order type (new/returning)

Thank you!
It works perfeclty

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.

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
Frequent Visitor

## Re: Specifying the order type (new/returning)

LOOKUPVALUE worked perfect. Thank you!

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 141 members 1,884 guests
Recent signins: