DAX count rows causing extra row

[kelint]

Hi,

I am trying to count new customers for each year (rule is if they haven't order in last 3 years they are considered new) and my code is shown below but I'm getting this extra 1 that I cannot figure out? Any thoughts?

New Customers =

VAR currentCustomers = VALUES(LSCI_OrderContacts[EndUserCompanyFinal])

VAR currentDate = FIRSTDATE(Calender[Date])

VAR oldDate = DATEADD(currentDate,-3,YEAR)

VAR pastCustomers = CALCULATETABLE(VALUES(LSCI_OrderContacts[EndUserCompanyFinal]),ALL(Calender[Date].[Year]),Calender[Date] < currentDate,Calender[Date] > oldDate)

VAR newCustomers = EXCEPT(currentCustomers,pastCustomers)

return COUNTROWS(newCustomers)

[parry2k]

@kelint I'm actually not sure how it is working, can you double-check that old date is getting calculated correctly. Create a new measure and return old date variable, want to see what value it returns.

[kelint]

HI @parry2k 

Thank  you - I think you are correct! I tried what you suggested and here is the result for olddate - any suggestions to fix ?

[kelint · Accepted solution]

Hi @parry2k ,

I fixed it by changing VALUES (bold below) to DISTINCT and it worked. I also changed the Date calculation as well. 

OLD CODE

New Customers =

VAR currentCustomers = VALUES(LSCI_OrderContacts[EndUserCompanyFinal])

VAR currentDate = FIRSTDATE(Calender[Date])

VAR oldDate = DATEADD(currentDate,-3,YEAR)

VAR pastCustomers = CALCULATETABLE(VALUES(LSCI_OrderContacts[EndUserCompanyFinal]),ALL(Calender[Date].[Year]),Calender[Date] < currentDate,Calender[Date] > oldDate)

VAR newCustomers = EXCEPT(currentCustomers,pastCustomers)

return COUNTROWS(newCustomers)

NEW CODE:

New Customers =

VAR currentCustomers = DISTINCT(LSCI_OrderContacts[EndUserCompanyFinal])

VAR currentDate = FIRSTDATE(Calender[Date])

VAR oldDate = DATEADD(currentDate,-3,YEAR)

 

VAR __minDate =

CALCULATE(

MIN( 'Calender'[Date] ),

ALL( 'Calender' ),

VALUES( 'Erp BookDtl'[BookDate] )

)

 

Var __oldDate = IF(oldDate =BLANK(),__minDate,oldDate)

 

VAR pastCustomers = CALCULATETABLE(VALUES(LSCI_OrderContacts[EndUserCompanyFinal]),ALL(Calender[Date].[Year]),Calender[Date] < currentDate,Calender[Date] > __oldDate)

 

VAR newCustomers = EXCEPT(currentCustomers,pastCustomers)

 

return COUNTROWS(newCustomers)

Thank you for your help.

Karen

[parry2k]

@kelint sorry for the late reply, glad it is fixed. cheers!!