Forum Discussion

Meagan's avatar
Meagan
Most Valuable Professional
10 years ago
Solved

Lost Customers - Issues with DAX Calculation

I'm having an issue with a DAX calc for lost customers and I'm hoping someone can spot where we went wrong. I have followed the pattern described in http://www.daxpatterns.com/new-and-returning-custo...
Need Help
  • marcorusso's avatar
    marcorusso
    10 years ago

    Good point - you have to remove some filter from the CustomerLostDate column expression.

    Try this one replacing the ADDCOLUMNS in original formula:

    ADDCOLUMNS (
    CALCULATETABLE ( VALUES ( Customer[CustomerCode] ), Sales ),
    "CustomerLostDate", CALCULATE (
        MAX ( Sales[Invoice Date] ),
        ALLEXCEPT ( Customer, Customer[CustomerCode] )
    )
        + [Lost Days Limit]
)
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi zkazimov

     

    Try this calculated field formula

     

    =COUNTROWS(FILTER(SUMMARIZE(CALCULATETABLE(VALUES(SALES[CUSTOMER]),DATESBETWEEN('CALENDAR'[Date],EDATE(MIN('CALENDAR'[Date]),-1),max('CALENDAR'[Date]))),[CUSTOMER],"ABCD",[TOTAL SALES],"EFGH",[TOTAL SALES PREV PERIOD]),[EFGH]>0&&ISBLANK([ABCD])))

    Hope this helps.

     

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi zkazimov,

     

    If my reply helped, please mark it as Answer.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi zkazimov,

     

    To keep things simple, try this calculated field formula

     

    =if(AND([TOTAL SALES PREV PERIOD]>0,[TOTAL SALES]=0),1,0)

    In the Visual level filters section, apply a criteria of 1.

     

    Hope this helps.

  • zkazimov's avatar
    zkazimov
    Helper I
    8 years ago

    Hi Ashish_Mathur

     

    That works as a workaround, but not able to aggregate that measure to show on for example usign Card Visual. Also in the table see below how it is not aggreagted for total lost customers.

     

    I need same effect as i have in the table for new customers.

     

     

  • zkazimov's avatar
    zkazimov
    Helper I
    8 years ago

    Ashish_Mathur

     

    This is great! Summarize Function did the job. Thanks a lot for time spent on this Appreciated. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I am having same issue, could you please share the DAX you created ?