cancel
Showing results for
Did you mean:
Highlighted
Helper II

How to approach a customers buying habits?

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV

Re: How to approach a customers buying habits?

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])
```

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

3 REPLIES 3
Highlighted
Super User IV

Re: How to approach a customers buying habits?

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])
```

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Helper II

Re: How to approach a customers buying habits?

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?
Highlighted
Helper II

Re: How to approach a customers buying habits?

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.

Announcements

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Top Solution Authors
Top Kudoed Authors