Forum Discussion
More complicated Repeat Customer Pattern
I'll give an example of what I'm trying to acheive, with some background:
The data I pull is from 1/1/19 to current day.
I'd like to see our repeat/return customers each month. I plan to display these in a monthly line graph and potentially a daily line graph using order_date as the x axis.
Example: When I click on January 19 in the graph, someone can qualify to be a repeat customer if she ordered a service on the 19th and on the 1st. When I click on the graph on January 1 in the graph, she should not appear--she had only booked one job at that point in time, so she was not a repeat customer at that time.
Does anyone know how to do this, or can point me in a good direction? I've only been using BI for 3 months.
If you have a data model that looks similar to this:
Then you can get the following output:
Using this type of calculation:
Repeat Customer Count = CALCULATE ( COUNTROWS ( 'Customer' ), FILTER ( 'Customer', CALCULATE ( COUNTROWS ( VALUES ( 'Data'[Date] ) ), FILTER ( ALL ( 'Date'), 'Date'[Date] <= MAX ( 'Date'[Date] ) ) ) > 1 && CALCULATE(COUNTROWS('Data')) >= 1 ) )I have uploaded a sample workbook here: Sample Workbook
2 Replies
If you have a data model that looks similar to this:
Then you can get the following output:
Using this type of calculation:
Repeat Customer Count = CALCULATE ( COUNTROWS ( 'Customer' ), FILTER ( 'Customer', CALCULATE ( COUNTROWS ( VALUES ( 'Data'[Date] ) ), FILTER ( ALL ( 'Date'), 'Date'[Date] <= MAX ( 'Date'[Date] ) ) ) > 1 && CALCULATE(COUNTROWS('Data')) >= 1 ) )I have uploaded a sample workbook here: Sample Workbook