cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
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
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!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
Highlighted
Super User IX
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!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Helper II
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:
AverageOrderFrequency.PNG
Nothing happens though. I was expecting a new column to appear in my table?
Highlighted
Helper II
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.

 

 

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

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?

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

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

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

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