Average lost sales of lost customers

[appley922]

Hi there,

I have raw data of sales by customer for the past 3 years, and I am trying to

1) highlight lost customers, defined by: Customer that has not bought anything for the past 12 months. 

2) highlight lost sales of above customers, defined by: Average monthly sales over the 6 months that precedes the past 12 months of no sales. 

Are these formulae correct?

Thank you so much.

Lost Customers =
VAR CustomersPurchased= CALCULATETABLE(VALUES('Raw Data'[Client ID (#)]),

FILTER(ALL('Calendar'),'Calendar'[Date] > MIN('Calendar'[Date]) - 720 && 'Calendar'[Date] < MIN('Calendar'[Date]) - 360))

VAR PriorCustomers=CALCULATETABLE(VALUES('Raw Data'[Client ID (#)]), FILTER(ALL('Calendar'), 'Calendar'[Date] > MIN('Calendar'[Date]) - 360 && 'Calendar'[Date] < MIN('Calendar'[Date])))
RETURN
COUNTROWS(EXCEPT(CustomersPurchased, PriorCustomers)) * -1

Average Lost Sales Value =
VAR CustomersPurchased= CALCULATETABLE(VALUES('Raw Data'[Client ID (#)]),FILTER(ALL('Calendar'), 'Calendar'[Date] > MIN('Calendar'[Date]) - 720 && 'Calendar'[Date] < MIN('Calendar'[Date]) - 360 ))
VAR PriorCustomers= CALCULATETABLE(VALUES('Raw Data'[Client ID (#)]),FILTER(ALL('Calendar'), 'Calendar'[Date] > MIN('Calendar'[Date]) - 360 && 'Calendar'[Date] < MIN('Calendar'[Date])))
RETURN
CALCULATE(
CALCULATE([Average Monthly Sales],
DATESBETWEEN('Calendar'[Date], MIN('Calendar'[Date]) - 720, MIN('Calendar'[Date])-360 )),
EXCEPT(CustomersPurchased, PriorCustomers)) * -1

Ave Monthly Sales = DIVIDE(CALCULATE(SUMX('Raw Data','Raw Data'[USD Total]),DATESINPERIOD('Calendar'[Date],MAX('Calendar'[Date]),-6,MONTH)),6,BLANK())

[v-piga-msft]

Hi @appley922 ,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best Regards,

Cherry

[v-piga-msft]

Hi @appley922 ,

It seems that your formulas have no syntax error. 

However, to check if it is correct, I'm afraid that you'd better to have a test with these formulas.

If you still need help, please share your data sample and your desired output so that we could help further on it.

Best  Regards,

Cherry