How to list customers who made 0 orders during current + previous 2 months

[Kroof]

Dear Community,

I am looking for a way to list customers who made 0 orders during current month and the previous 2 months.

The data in the orders table (TableA) includes

• customerID
• order_number
• period of the order (in format yyyy.mm) over the last 10 years.

I expect to see a list of those customers who did make at least one order in the past (thus in the table) but there is no order in the table recently.

My thinking so far:

• I need to be able to count the orders during the latest N periods against all customerID and select the 0 (or null?) values.
• My experience is that if I create a matrix table in reporting view counting order_number and filter for the TOP N value for period amongst the filters then I get only those customers who made orders. I assume this is working the same way in case of the count measure. 
• Thus I imported the same table into a new table: TableB (via PowerQuery) but kept only the customerID and removed duplicates and got the distinct list of customerIDs. I was trying to add a countX function using FILTER against the original table. I am stuck now to add additional condition into the function to filter to the TOP 3 values. My latest function in the TableB looks like this:
  • CountOfOrders =  COUNTX( FILTER(Table_A, TableA[customerID] = TableB[customerID]), TableA[order_number])

Many thanks in advance,

Kroof

[tamerj1]

Hi @Kroof 
Please try

CountOfOrders =
COUNTX (
    TOPN (
        3,
        FILTER ( Table_A, TableA[customerID] = TableB[customerID] ),
        Table_A[period]
    ),
    TableA[order_number]
)

[Kroof]

Thanks for the reply. This solution counts those periods where there is at least one order, and not the last 3 thus I never get a 0 count. Can we create a list or variable and inject into the FILTER function? I tried this but I get an error.

CountOfOrders = 
    VAR Last3Periods =
        TOPN(
            3,
            TableA,
            TableA[period]
        )
    return
        
COUNTX (
    FILTER(TableA, 
        TableA[customerID] = TableB[customerID]
        && 
        TableA[period] = Last3Periods
    )
    TableA[order_number]
)

WHat shall I amend?

[tamerj1]

@Kroof 
Is there any relationship active on TableB?

[Kroof]

yes, TableA and TableB is related n:1 based on customerID.

[tamerj1]

@Kroof 
Ok, that should make it even easier. But what is the data type of the YYYY.MM [period] column? Is it date or text?

[Kroof]

text

[tamerj1 · Accepted solution]

Hi @Kroof 
You need first to create a new calculated column in TableA of integer data type that ranks the period column as folows:

Period Rank =
RANKX ( TableA, TableA[period],, ASC, DENSE )

Then the calculated column in TableB would be

CountOfOrders =
VAR RelatedTableA =
    RELATEDTABLE ( TableA )
VAR LastPeriod =
    MAX ( TableA[Period Rank] )
RETURN
    COUNTX (
        FILTER (
            RelatedTableA,
            TableA[Period Rank] >= LastPeriod - 2
                && TableA[Period Rank] <= LastPeriod
        ),
        TableA[order_number]
    )

[Kroof]

Applied and double checked the results. Works like a charm. Many thanks!