Lost Customers Calculation Formula

[Anonymous]

Hello,

I use this formula below and it works fine. But if i filter and see region-wise lost customers pbi recalculates for every filter which i don't want.

I need customers to be counted as lost if there's no purchase last x days at anywhere, no matter which regions filtered.

For example, customer might be purchased sth @ place X, but didn't purchased last 100 days @ place Y. This customer shouldn't be counted as lost In theory it's lost for place Y but it's still our customer. How can i differentiate formula for this purpose?

sample screenshot of a simple calculated table...

..added data relationship screenshot if needed.

LostCustomer = SUMX(VALUES('Customer Data'[NEW BOOKING CUSTOMER]);IF('Customer Data'[CustomerLostDate]>=[FirstDateSelected] && 'Customer Data'[CustomerLostDate]<=[LastDateSelected];1))

FirstDateSelected = FIRSTDATE(DATEADD('Date'[DATE];0;MONTH))

LastDateSelected = LASTDATE(DATEADD('Date'[DATE];0;MONTH))

CustomerLostDate = CALCULATE(MAX('New Customer Check Table'[DATE]);CALCULATETABLE('New Customer Check Table';FILTER(ALL('Customer Data');'Customer Data'[NEW BOOKING CUSTOMER] = MAX('Customer Data'[NEW BOOKING CUSTOMER]));FILTER(ALL('Date');'Date'[DATE]<=MAX('Date'[DATE]))))+[LostDaysLimit]

LostDaysLimit = [DaysCustomerLost]+1

Thanks,

Caglar

[v-ljerr-msft]

Hi @Anonymous,

Where did you filter the regions, and which column is it? I cannot see it in your shared screenshots.

In addition, could you share a sample pbix file with just some mock data which can reproduce the issue, so that we can better assist on it? 

Regards

[Anonymous]

Hi @v-ljerr-msft,

I couldn't get same results with a mock .pbix file so i share the original one 🙂

POL refers to regions by the way,

here is the link,

 .pbix

https://drive.google.com/open?id=0B_SyDMpXEmrWamFHbGRfRDZLaFU

excel raw data

https://drive.google.com/open?id=0B_SyDMpXEmrWSFR5M2E2dWJpbGs

Thx,