Determining when a customer stopped trading

[melkbos]

Hi everyone I am hoping someone here can help.

I have a Data table listing all monthly sales of a single product. In the Data table the fields represented in each row are:
- Customer
- Financial Period (in this case 1 = July as the company has a 30 June year end)
- Financial Year
-- Volume in units

I want to be able to either put a flag in an extra column on this table, or indicate on a Summary table when and if a customer churned.

The definition of churn is still up for debate, but let's assume for the sake of getting an answer that a customer is defined as having churned if sales volume in the current month is less than 20% of the average for the previous 3 months.

I can probably get to the answer in a very messy way but I was hoping there is a more eloquent solution. I think the answer may lie in the EARLIER function but I don't have a good handle on that.

Example (showing just one customer and data for one year when data set spans all customer and 30 months of data):
Customer 1, 1, 2015, 150
Customer 1, 2, 2015, 120
Customer 1, 3, 2015, 120
Customer 1, 4, 2015, 120
Customer 1, 5, 2015, 120
Customer 1, 6, 2015, 120
Customer 1, 7, 2015, 100
Customer 1, 8, 2015, 100
Customer 1, 9, 2015, 100
Customer 1, 10, 2015, 18
Customer 1, 11, 2015, 12
Customer 1, 12, 2015, 0

Somewhere I need to identify that the customer churned in period 10 of 2015.

Any help welcome.

Thanks

 

[Matt Allington]

I am sure I have answered this question somewhere else already yesterday or today. Take a look at this
New and Returning Customers – DAX Patterns

the dax is hard but easy to copy. You really need to be clear what your definition of churn is before you can move forward.