How to approach a customers buying habits?

[Anonymous]

Hello,

I want to use i.e. traffic lights to display the status of a customer. Basically I want to show a red light if the customer have not made any order for a longer period of time, compared to his buying pattern (history). For instance if a customer have ordered things usually every week for a long time, and it is now two weeks since his last order, I want to show a red light.

I don't know how I should approach this, especially how to determine the customers buying pattern, and then how to compare that to the last period....

Anyone that have solved a similar problem?

Any help would be deeply appreciated.

[Greg_Deckler · Accepted solution]

I would create a measure that calculates the AVERAGE for a customer. Perhaps like:

Measure 7 = 
VAR __customer = MAX('Table5'[Customer])
VAR __table = FILTER('Table5',[Customer] = __customer)
VAR __table1 = ADDCOLUMNS(__table,"__days",DATEDIFF(MAXX(FILTER(__table,[Date] < EARLIER([Date])),[Date]),[Date],DAY))
RETURN
AVERAGEX(__table1,[__days])

[Anonymous]

Thanks for your answer Greg, much appreciated. I have tried to apply your answer to my data but my knowledge about measures is very limited. Based on your example I tried the following on my specific data:

Measure 7 =

VAR __customer = MAX('Customer Order Frequency'[Sell-to customer])

VAR __table = FILTER('Customer Order Frequency';[Sell-to customer] = __customer)

VAR __table1 = ADDCOLUMNS(__table;"__days";DATEDIFF(MAXX(FILTER(__table;[Order Date] < EARLIER([Order Date]));[Order Date]);[Order Date];DAY))

RETURN

AVERAGEX(__table1;[__days])

And my data table looks like this for an example customer:

Nothing happens though. I was expecting a new column to appear in my table?

[Anonymous]

I finally got it to work, and as I said, it was my lack of understanding of measures. Once I got a hang of that, it was pretty straight forward. Thanks Greg.