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

 

 

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.

  • 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 ?