Calculating New Accounts and "Winback" Customer Orders

[brian123]

Hi,

You all were so helpful last time I got stuck, I figured I would ask again!

We have a sales team that gets new account commission on the opening orders of brand new accounts, but they also get new account commission on a "winback" customer (same commission rate so they don't need to be calculated separately). A Winback is defined as a customer that has gone 12 months without purchasing. The commission is only awarded on the first order, and only if the order exceeds $500. All of these metrics are also displayed on a visual within the context of a single month (which is filtered on the page level). So I need to be able to say the total value of new+winback accounts opened within the month that is filtered upon.

I have a sales header table (with order #, date, customer #), a sales line table (with order#, customer #, sales amount $), a customer table, and a date table. 

My thought is to evaluate each order individually, and see if the customer on that order had sales in the prior 12 months, if not, they would be deemed a new customer. 

Ideas?

Thanks in advance for any insight!

-Brian

[amitchandak · Accepted solution]

I think, I also missed. Earlier is needed

Last Order Date = MAXX(FILTER('Sales Document Header','Sales Document Header'[Sell-to Customer No_]=earlier('Sales Document Header'[Sell-to Customer No_]) && 'Sales Document Header'[Posting Date]<earlier('Sales Document Header'[Posting Date])),'Sales Document Header'[Posting Date])

[brian123]

To provide some additional context about another way I have tried to calculate this. I calculated the amount of sales in the datesbetween the posting date, and 365 days prior. If that sales amount = 0, then I knew it was a new or winback order. The problem with my calculations is that I can display the correct answer summarized at the customer level, but I cannot display it summarized at the order level (which is imperative). If I try to summarize at order level, it just loads forever and my memory resources time out. 

Posted Sales (past 365) = CALCULATE([Posted Sales],DATESBETWEEN('Posting Date'[Date],MIN('Sales Document Header'[Posting Date])-365,MIN('Sales Document Header'[Posting Date])-1))

New or Winback Sales = IF([Posted Sales (past 365)]=0,[Posted Sales],0)

New or Winback Summarized = sumx(SUMMARIZE(Customer,Customer[No_],"New Biz",[New or Winback Sales]),[New Biz])

[amitchandak · Accepted solution]

I think, I also missed. Earlier is needed

Last Order Date = MAXX(FILTER('Sales Document Header','Sales Document Header'[Sell-to Customer No_]=earlier('Sales Document Header'[Sell-to Customer No_]) && 'Sales Document Header'[Posting Date]<earlier('Sales Document Header'[Posting Date])),'Sales Document Header'[Posting Date])

[amitchandak]

This is way you can do it using all new columns.  You can also use new measures to do some of these calculations. New Measure need to values(SalesHeader[Cutomer#]) to make sure data grouped at the customer level

Last order date = maxx(filter('sales header','sales header'[customer #]= 'sales header'[customer #] && 
								'sales header'[Date]< 'sales header'[Date]),'sales header'[Date])


Last before Month = datediff('sales header'[Date],maxx(filter('sales header','sales header'[customer #]= 'sales header'[customer #] && 
								'sales header'[Date]< 'sales header'[Date]),'sales header'[Date]),month)
or 
Last before Month =datediff('sales header'[Date],Last order date,month)

Sales Amount in header = sumx('sales line table','sales line table'[sales Amount])

Flag as new column = if('sales header'[Last before Month]>=12 && 'sales header'[Sales Amount in header]>500,"winback",blank())

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[brian123]

@amitchandak thanks for your reply

Unfortunately, when I try to input a calculated column for Last Order Date, it only returns blanks

Last Order Date = MAXX(FILTER('Sales Document Header','Sales Document Header'[Sell-to Customer No_]='Sales Document Header'[Sell-to Customer No_] && 'Sales Document Header'[Posting Date]<'Sales Document Header'[Posting Date]),'Sales Document Header'[Posting Date])

I understand your logic, and I can make the rest of it work if I could just figure out this first part. We want the last sales date prior to the current sales date we are looking at, for a specific customer. This calculation needs to be displayed on a table with order numbers (such as the sales header), but it is calculated by grouping at the customer level. 

any more help is appreciated, thanks!

[amitchandak]

The last Date will only come when the customer comes the second time. Is the complete column is blank?  We can subtract the current date from last date we can know after how much time, the customer is back and can create logic on top of it

[brian123]

@amitchandak  yes the entire column is blank. Subtracting current date (today) from last sales date will not work, as I need to know the number of days between last sales date and the date prior to last sales date.

@Ashish_Mathur  Here is a very basic concept of what I am trying to achieve. All I need is the "Previous Order Date" or "Days since previous order" calculation and I can take it from there. In the context of an order table with multiple customers, and multiple orders per customer, I need to be able to say (for each order), how many days it has been since that customer has last ordered. If that number is greater than 365, I can trigger my Winback calculations. I can find first sales date or last sales date, but finding the previous sales date is proving difficult. 

Here is a quick mock up of how I would display it. I have highlighted just one customer's orders, even though there are several customers on this table, and the calculations would be done for every line. 

Thanks again for any additional comments!

[Ashish_Mathur · Accepted solution]

Hi,

Write this calculated column formula to calculate the previous order date

=CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Sell-to-Customer No_]=EARLIER(Data[Sell-to-Customer No_])&&Data[Date]<EARLIER(Data[Date])))

Hope this helps.

[Ashish_Mathur]

Hi,

Share a simple dataset and show the expected result.