How to find New Customers per Sales Rep YoY

[Jhartigan]

Hello,
I have a powerbi data where each line item is a transaction. The dataset has:

• Customer Number
• Sales Rep associated
• Date of the sale

I am trying to make a matrix table where the rows are the sales rep and the values are the number of new customers they had in comparison to previous year (specifically 2023 in comparison to 2022)

Attached is a picture of roughly what I am looking for:

Thank you!

 

[Ashish_Mathur]

Hi,

Assuming you have data only till March 2023, when you compare with a previous year, do you want to compare with Jan-Mar 2022?  Also, share data in a format that can be pasted in an MS Excel file.

[v-yiruan-msft · Accepted solution]

Hi @Jhartigan ,

I created a sample pbix file(see the attachment), please check if that is what you want. 

%change from 2022 = DIVIDE(([2023 New Customers]-[2022 Customers]),[2022 Customers])

In addition, you can refer the following links to get it:

How to find out new customers?

New customers =
VAR currentYear = MAX ( Sales[Year] )
VAR currentCustomers = CALCULATETABLE ( VALUES ( Sales[Company] ), Sales[Year] = currentYear )
VAR pastcustomers = CALCULATETABLE ( VALUES ( Sales[Company] ), Sales[Year] < currentYear )
VAR newcustomers = EXCEPT ( currentCustomers, pastcustomers )
RETURN
    COUNTROWS ( newcustomers )

How to calculate the percentage growth by years us...

Year Growth = 
VAR _previous = CALCULATE(SUM(Sheet3[Profit]),FILTER(ALLSELECTED(Sheet3), Sheet3[Year] = MAX(Sheet3[Year]) -1))
VAR _current =  CALCULATE(SUM(Sheet3[Profit]),FILTER(ALLSELECTED(Sheet3),Sheet3[Year] =MAX(Sheet3[Year])))
return
IF(_previous<>BLANK(),DIVIDE(_current,_previous,0)-1,BLANK())

Calculate Percentage Growth Over Time with Power BI

Usage Difference = 
      VAR _CurrentMonthUsage = TenantProductUsage[Current Usage]
      VAR _PreviousMonthUsage = TenantProductUsage[PriorMonthActiveUsers]
      VAR _Result = _CurrentMonthUsage -  _PreviousMonthUsage
      RETURN _Result

If the above one can't help you, could you please provide some raw data in your table (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Jhartigan]

Thank you v-yiruan-msft!

The pbi sample filed helped a lot, there were a few issues with if a customer also bought in a previous year that they then would still be counted as new, but the first formula you linked for new customers worked like a charm. Here is the formula I used below and its explanations for further understanding:

VAR currentyear = MAX('Table'[Year])

VAR currentCustomers = CALCULATETABLE( VALUES('Table'[CustomerNumber]), 'Table'[YearofSale] = currentyear )

VAR pastcustomers = CALCULATETABLE( VALUES('Table'[CustomerNumber]), 'Table'[YearofSale] = 2022)

VAR newcustomers = EXCEPT( currentCustomers, pastcustomers)

RETURN

    COUNTROWS( newcustomers)

The reason *Pastcustomer* is just for 2022 is because they wanted to show the number of new and repeat customers just from last year to this year.

Thank you again!