cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mandy22 Frequent Visitor
Frequent Visitor

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.

1 ACCEPTED SOLUTION

Accepted Solutions
fenchurchio Regular Visitor
Regular Visitor

Re: More complicated Repeat Customer Pattern

If you have a data model that looks similar to this:

2019-08-09_17h03_26.pngThen you can get the following output:

2019-08-09_16h59_21.png

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

 

 

View solution in original post

2 REPLIES 2
fenchurchio Regular Visitor
Regular Visitor

Re: More complicated Repeat Customer Pattern

If you have a data model that looks similar to this:

2019-08-09_17h03_26.pngThen you can get the following output:

2019-08-09_16h59_21.png

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

 

 

View solution in original post

Mandy22 Frequent Visitor
Frequent Visitor

Re: More complicated Repeat Customer Pattern

Thank you for your solution--it worked!

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (4,936)