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 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)
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

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    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'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