New Customer Count


#1

Hi Sam - I have a DAX code below for new customer count based on your tutorial. The code works great and currently gives me the count for new customers in a current year ‘churn period’ that weren’t there in the same PY ‘churn period’. However, I need it to do one more functionality. At the end, I need it to countrows only for the new customers, where “new customer sales” is not equal to zero.

Could you help me figure out how to add this caveat in my code at the end. Thanks!

Param_NewCustomers = //Defined as unique customers (shipTo#) that purchased units in the current year churn day period, but did not in the prior year churn day period

VAR ChurnDays = [SelectedChurnValue]

//Get the PY end date for the corresponding year
VAR PYLastDateCorrespondingYear = LASTDATE(DATEADD('Dim-Date Table'[Calendar Date],-365,DAY))

//1. Table consisting of dates of churn period from prior year
VAR PYChurnDates = FILTER (ALL('Dim-Date Table' ),
            'Dim-Date Table'[FiscYear] < MAX('Dim-Date Table'[FiscYear]) &&
            'Dim-Date Table'[Calendar Date] > PYLastDateCorrespondingYear  - ChurnDays &&
             'Dim-Date Table'[Calendar Date] <= PYLastDateCorrespondingYear )
            
//2. Identifies a unique list of all customers in the PY churn period
VAR PYCustomers = CALCULATETABLE (VALUES ('Fact-Sales'[Shipto#] ),PYChurnDates,'Fact-Sales'[Sales$]<>0)

//3. Create a date range table from the begining of the CY churn period to the end of the period
VAR CYChurnDates = FILTER(ALL ( 'Dim-Date Table' ),
            'Dim-Date Table'[Calendar Date] > MAX('Dim-Date Table'[Calendar Date]) - ChurnDays && 
            'Dim-Date Table'[Calendar Date] <= MAX ('Dim-Date Table'[Calendar Date]))

//4. Identifies a unique list of customers in the CY churn date period
VAR CYCustomers = CALCULATETABLE (VALUES ( 'Fact-Sales'[Shipto#] ),CYChurnDates,'Fact-Sales'[Sales$]<>0)

//5. Return a count of customers that are CY customers but weren't customers in the PY churn day period. IF statement on the date filters out periods that aren't relevant.
Return IF(DATEDIFF(CALCULATE(FIRSTDATE('Dim-Date Table'[Calendar Date]),PYChurnDates),CALCULATE(LASTDATE('Dim-Date Table'[Calendar Date]),PYChurnDates),DAY) < (ChurnDays-1),
    BLANK(),
    COUNTROWS(EXCEPT(CYCustomers,PYCustomers)))

#2

Probably going to need more information here around the model and also need to see the results you currently have in a table and then it will give me a better idea of what is required.

There’s obviously quite a lot going on with formulas like this and it’s very difficult to just guess the write formula without knowing more, and testing or replicating it myself.