KPI to show Number of Lost Customers

[jlg]

Hi. 

I have a data set where I am trying to show lost customers. 

I have created a lost customer Indicator that shows a 1 for a lost customer, and a 0 for non-lost customers, based in whether a customer had more than 2k of sales 2-4 months ago, but 0 sales in the last 2 months. All seems to work well in a table as below:

Where I am stuck is how to display this as a KPI on a dashboard. I want a count of the customers that have a Lost Customer Indicator of 1, but can't work out how to do this.

Any help appreciated

Thanks

[GaryGiles · Accepted solution]

Your KPI metric expression will be something like this:

=count({<[Delivery Name]={"=sum({$<[Sales Date]={"">=$(=AddMonths(Today(),-4))<=$(=AddMonths(Today(),-2))""}>} [Sales])>2000 and
sum({$<[Sales Date]={"">=$(=AddMonths(Today(),-2))""}>} [Sales])=0"}>}Distinct [Delivery Name])

[Delivery Name], [Sales Date], and [Sales] will need to be changed to the appropriate field names in your data model.  And if your Lost Customer Inidicator is different than what I used, it will need to be changed, but concept will remain the same.