Hi Fellow Power BI Users -
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)))