Calculation New, Repeat Customer

[ADSL]

Hi BI Community Team,

I have a sales order table (Aug'23 till Oct'23), with this I have created a measure below to find the customer purchased:

Customer Purchased = CALCULATE(DISTINCTCOUNT('Sales Order'[Customer Code]), 'Sales Order'[InvoiceType] = "F2", 'Sales Order'[OrderType] = "SALE")

So now we're looking for New Customer & Repeat Customer that calculation are current month compare to previous month.

Example: we're standing in Oct'23, then compare with Sep'23. If customer can see/find in Oct'23 => Repeat. If customer cannot find then it's New. Actually, customer purchased = Repeat + New

Sample file - https://drive.google.com/drive/folders/1UzgG-CXW3G0diCSfILUYRqXq9piZWZLj?usp=sharing 

Any suggestion/advise to find it?

Thanks and Regards,

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[ADSL]

Hi @Ashish_Mathur,

Thank you for your helpful feedback.

In sales order table, it have for both customer order and return then when we calculate and find the customer purchased, we need to count and filter only for 02 column name 'Sales Order'[InvoiceType] = "F2", 'Sales Order'[OrderType] = "SALE").

If we don't filter these columns then number of customer purchased are more than because it count for customer return too.

So any suggestion/advice of your meause?

Thanks and Regards,

[Ashish_Mathur]

Hi,

Revise my measures to:

New customers = CALCULATE([Customer Purchased],FILTER(VALUES('Sales Order'[Customer Code]),[Date of first interaction]>=MIN('Calendar'[Date])))

Repeat customers = CALCULATE([Customer Purchased],FILTER(VALUES('Sales Order'[Customer Code]),[Date of first interaction]<MIN('Calendar'[Date])))

I do not see any change in the answer though.

[ADSL]

Hi @Ashish_Mathur,

After revised and follow your new measure, then I am mapping with manual calculation. It's different.

Screenshot below, I am doing in manual by mapping between sales order in Sep'23 and Oct'23.

> Customer purchased => customer have order

> Repeat => custoemr have order in current month and last month.

> New => customer have order in current month and never have any order in last month

> These calculation are required to excluded == Sales Order'[InvoiceType] = "F2", 'Sales Order'[OrderType] = "SALE")

Please kindly help!

Sample link - https://drive.google.com/drive/folders/1UzgG-CXW3G0diCSfILUYRqXq9piZWZLj?usp=sharing 

Thanks and Regards,

[Ashish_Mathur]

My formula is correct.  Please check the data thoroughly.

[ADSL]

Hi @Ashish_Mathur ,

When I copy the list of customer code order in Oct'23 and match the customer code order in Sep'23, if found is "Repeat" and Not found is "New" then see the result below.

Is it possible that still count from these column Sales Order'[InvoiceType] = "F2", 'Sales Order'[OrderType] = "SALE"? 

Any suggestion/advise?

[Ashish_Mathur]

Hi,

Prepare a simple Table visual as seen in the screenshot below.  See which Customer codes are not being identifies correctly.  DIY.

[ADSL]

Hi @Ashish_Mathur,

I have followed your advise then found the result after choose a few customer to mapping with manual.

1. New --- customer code: CT01718 have one transcation in Oct'23 but your calculation is showing in "Repeat". It should be in "New"

2. Repeat --- customer code: CT03498 have 02 transcation in Oct'23, your calculation is showing "Repeat", it should be in "New" because transcation are in the same month.

=> Even customer have one order or many in the same month, we still call "New" if they didn't have order in the last month.

=> For customer  have order in the last month, and current month are also have then called " Repeat".

=> column that we count are InvoiceType "F2" and OrderType "SALE". these column are refer to order and sales, beside it's return.

Sample file https://drive.google.com/drive/folders/1UzgG-CXW3G0diCSfILUYRqXq9piZWZLj?usp=sharing 

Any suggestion?

[Ashish_Mathur]

Hi,

CT01718 has transactions in Aug and Sep as well.

The same if true for CT03498 as well.

Please recheck.

[ADSL]

Hi @Ashish_Mathur,

Yes, however we're comparing current and last month to find how many new and repeat of customer in the current that we stand.

So all questions that raised in Oct and compare to Sep. If we're standing in Sep then compare to Aug.

Please kindly check and advise.

Thanks and Regards,

[Ashish_Mathur]

In the month filter drop down, select only one month - October.  Both those codes have transactions in September and therefore for the month of October, they should both be classified as Repeat.

[ADSL]

Hi @Ashish_Mathur,

One customer have multiple sales team serviced (sell different brand/category), if you check this customer code: CT01718 will see transcation from Aug till Oct. But we filter by sales team = Food1, Brand = Kingfisher then have transcation in Aug & Oct. 

So if we're standing in Oct, then this customer is New because no transcation in Sep.

Please check and advise.

[Ashish_Mathur]

Hi,

Please check now.  File attached.

[ADSL]

Hi @Ashish_Mathur,

I have checked your file, the number is more than I count manually in excel.

This result is distinct count in excel file.

Please check and advise.

[Ashish_Mathur]

How do you expect me to help you with just that statement.  DIY.  Solve it by yourself now.

[ADSL]

Hi @Ashish_Mathur,

Just feedback what I see however still need your support and advise. sometimes we misunderstand or wrong explain then result also are different.

Thanks and Regards,

[Ashish_Mathur]

I really do not know how ealse to help you.  Create a small dataset and try my formulas on that small dataset.  See if you get the perfect result there or not.  If there is a problem even on that small dataset, then we will revise the formula.  Finally we will then plug in your large dataset.  Please send time on doing this checking yourself.

[ADSL]

Hi @Ashish_Mathur ,

Well noted. Thank you very much for your value time and help.

Best Regards,

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[lbendlin]

New and returning customers – DAX Patterns