Count the Number of Customer Order in Current Month vs. Last 03 month

[ADSL]

Hi BI Community Team,

I have 02 tables of sales order on the current month --- Nov'22 & last 03 month --- Aug'22 till Oct'22.

And we want to find & count the "New Customer" by sales rep in Nov'22. In excel, we filter by sales rep code of both tables and mapping of customer code if there are no duplicate of red color in Nov'22 then it's called "New Customer". It's taking so long time to do one by one of sales rep.

Any suggestion of measure in power to calcuate it?

Thanks and Regards,

[Aburar_123]

Hi @ADSL ,

Please find the below logic,

Please Accept this post as a Solution if it solves your problem. Thanks.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[ADSL]

Hi @Ashish_Mathur ,

As per your file result are count and show only 05 but I am manually count in excel and found 17 for this sales rep - 10750175

Thanks and Regards,

[Ashish_Mathur]

Hi,

There are 2 conditions i am testing for.  If November is selected, then there should be a sale in November and sum of sales in the previous 3 months should be 0.  Apply the same conditions to your dataset. 

[ADSL]

Hi @Ashish_Mathur,

Here is the report expection that finding "New" customer. customer order in current month vs. last 03 month:

If November is selected, then customer order in November will be mapping with sales order for last 03 months.

If it found customer have no order in last 03 month and only have in November then it's call "New Customer". 

Any suggestion? 

[Ashish_Mathur]

What do you mean by "Here is"?

[ADSL]

Hi @Ashish_Mathur,

I mean that if you can check and review the question again because I am concern the previous post was not clear.

However you always supported and provided the suggestion. Thank you for your helpful feedback.

Thanks and Regards,

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[HiraNegi]

Hi,

Refer to below sample data.

NewSales table data:

OldSales table data:

Create measure in NewSales table to check if the Customer is new or old.

Note: This does not require relationship between the two tables.

CheckNewCustomers =

var vCustomer = SELECTEDVALUE(NewSales[Customer])

var vSalesman = SELECTEDVALUE(NewSales[Salesman])

var vOldSales = CALCULATETABLE(OldSales, FILTER(OldSales, OldSales[Customer] = vCustomer),

    FILTER(OldSales, OldSales[Salesman] = vSalesman))

Return

IF(COUNTROWS(vOldSales) >= 1, "Old", "New")

This will give you the results like below from NewSales table.

Hope this helps!

[ADSL]

Hi @HiraNegi,

After trying your suggestion, we are able to see the "New" & "Old" customer as the screenshot below.

Any suggestion if we want to see and count the "New" customer only as the screenshot below? 

Thanks and Regards,

[HiraNegi]

Hi @ADSL ,

Once you have a measure to categories New and Old customer, you can create another measure to take the count like below.

NewCustomerCount = CALCULATE(COUNT(NewSales[Customer]), FILTER(NewSales,[CheckNewCustomers] = "New"))

[ADSL]

Hi @HiraNegi ,

It has some variant number counting of New customer after followed your measure suggestion above. Please check the screenshot below.

1st measure, it's only 17.

2nd measure, it's 72 customer.

[HiraNegi]

Hi @ADSL ,

Not sure what is the data granularity of your report. But can you try using DISTINCT to measure expression like below.

NewCustomerCount = CALCULATE(DISTINCTCOUNT(NewSales[Customer]), FILTER(NewSales,[CheckNewCustomers] = "New"))

Regards,

Hira Negi

[FreemanZ]

hi @ADSL 

try to 

1) get a customer list from Aug to Oct, like CALCULATETABLE(VALUES(), FILTER(...))

2) get a customer list in Nov

3) make an EXCEPT of the two

If you need further suggestion, consider provide some sample raw data and @me. 

[ADSL]

Hi @FreemanZ,

I am a new Power BI user then still need your suggestion and detail of measure.

Here is the link of sample pbix file - https://drive.google.com/drive/folders/1lACpCBHOAjWBbdTijkLjzc62hUOU9CMM?usp=sharing 

Thanks and Regards,

[FreemanZ]

hi @ADSL 

Could you paste a table here?

as Google is blocked here.😂

[ADSL]

Hi @FreemanZ ,

I am unable to post the attachment file and only share the link to download.

However you can see the screenshot below as the sales order that it's the same format from Aug'22 till Nov'22.

https://drive.google.com/drive/folders/1lACpCBHOAjWBbdTijkLjzc62hUOU9CMM?usp=sharing