Matrix row totals not working properly

[quikin5]

Hi, I have a matrix with year, month (both from a calendar table), and customer as row headers. As for values I have the following measure: 

Lost Customers Amount =

VAR currentCustomers = VALUES(SALES[Customer])

VAR previousMonthDates = PREVIOUSMONTH('DATE'[Date])

VAR previousMonthCustomers = CALCULATETABLE(SALES[Customer]),ALL('DATE'[Date]), previousMonthDates)

VAR LostCustomers= EXCEPT(previousMonthCustomers,currentCustomers)

VAR LastMonthAmount = CALCULATE(SUM(SALES[Sales]), DATEADD('DATE'[Date], -1, MONTH), LostCustomers)

RETURN

    SUMX(LostCustomers, LastMonthAmount)

The measure returns the sales of the customers who purchased the previous month but did not purchase the selected month.

The measure works fine in the matrix at customer level, but fails to aggregate the sum properly at month level.

Any idea would be very appreciated! 

[amitchandak]

@quikin5 ,

In such case create a new measure ,

Sumx(Values(VALUES(SALES[Customer]), [Lost Customers Amount])

Also check my approach of just using isblank and avoid expcept

Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://youtu.be/EyL7KMw877Q

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...

[quikin5]

It didn't work 😞