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 IX

## 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 IX

## 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

#### Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

#### August 2020 Community Challenge: Can You Solve These?

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

#### July 2020 Community Highlights

Learn about the exciting things that happened in July.

#### Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

#### Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors