Forum Discussion

kelint's avatar
kelint
Frequent Visitor
6 years ago
Solved

DAX count rows causing extra row

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 fig...
Need Help
  • kelint's avatar
    kelint
    6 years ago

    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
kelint's avatar
kelint
Frequent Visitor
6 years ago

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's avatar
kelint
Frequent Visitor
6 years ago

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's avatar
    parry2k
    Super User
    6 years ago

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