Number of customers who placed at least X orders over the last X months

[Anonymous]

Hi,

I’ve been struggling with this issue for the last couple of hours. Perhaps you can help me out with it.

I have an ORDER table with order dates and customer ID’s, this is a simplified version of that table:

Order date	Customer ID
Jan 1 2018	AA1
Jan 2 2018	AA1
Jan 3 2018	BB2
Feb 1 2018	AA1
Feb 1 2018	CC3
Feb 1 2018	DD3
Feb 1 2018	AA1

I also have a separate date table that is marked as a date table, and which is linked to this ORDER table.

I’ve tried creating the following four measures:

1. Number of customers who placed 2 or more orders in this month – Don’t know how to do this. I’ve tried doing a distinctcount where the orderdate count is higher than 1, but that won’t work.
   February result would be 2.
2. Number of customers who placed at least 1 order this month – FIXED - this one is just a distinctcount of the amount of customer ID’s.
   February result would be 3
3. Number of customers who placed at least 1 order over the last 3 months – I’ve tried doing this with the DATEADD function but it will still only return the results of the current month
   February result would be 4
4. Number of customers who placed no orders or last placed their order more than 3 months ago – FIXED – this will be the leftovers.
   February result would be 0

What I’m struggling with is that I want to show these numbers in a report, and also want to show the Month over month increase/decrease for each of these segments. Any help is greatly appreciated!

Thanks

Bas

[Anonymous]

I loaded your example data as table1 into a pbix. The following measures give what you want

Distinct Customer Count = DISTINCTCOUNT ( Table1[Customer ID] )

Two Plus Orders =
CALCULATE (
    [Distinct Customer Count],
    FILTER ( Table1, CALCULATE ( COUNTROWS ( Table1 ) ) >= 2 )
)

At least one order in L3M =
CALCULATE (
    [Distinct Customer Count],
    DATESINPERIOD (
        Table1[Order Date].[Date],
        ENDOFMONTH ( Table1[Order Date].[Date] ),
        -3,
        MONTH
    )
)

I have used the inbuilt date table for ease, however if possible you should use a seperate date table and use that instead of the .[Date] notation.

[Anonymous]

Thank you for the quick reply @Anonymous!

Both formula's don't seem to work for me. But as they work perfectly from your screenshot I think this might has something to do with the rest of my model.

The two plus orders (segment 1) returns blanks, and the At Least One Order in L3M (segment 3) returns the same results as the distinctcount formula (segment 2). 

For Segment 1 I've used:

Segment 1 = CALCULATE(
CALCULATE(
    DISTINCTCOUNT('Sales Data'[Customer ID]),
    FILTER('Sales Data',CALCULATE(COUNTROWS('Sales Data') >= 2)) 
    )

I've tried the same calculation by doing a sum of the amount of orders, but it won't work either. I've checked the data in Excel and there are plenty of customers who have ordered more than twice every month. Could this have anything to do with my date filters in my report? I don't have any page or report filters actived.

Segment 2 is just a distintcount

CALCULATE(DISTINCTCOUNT('Sales Data'[Customer ID]))

For Segment 3 I now have:

CALCULATE(
    DISTINCTCOUNT('Sales Data'[Customer ID]),
     DATESINPERIOD (
        _Date[Date],
        ENDOFMONTH (_Date[Date]),-3,MONTH)
    )

_Date is my date table.

I don't understand what the .[date] part in your formula does, when I add it here things get broken. How should I interpret that?

Thanks!
Bas