Measures using measures as filters

[evolu6ion]

Hello everyone,

Today, I am stuck at making a measure that has to deal with filters based on another measure result.

Here is a sample of my data and the problem that is appearing:

In the matrix, I have 3 different customers and their purchases behavior. In the wide red rectangle, you can see that as long as their latest purchase date is farther than 24 months from the end of the current row month, the customer should not be calculated with the 'Nb of customers 24 months' measure. When this measure is in such matrix, it behaves as it should. But whenever I remove the 'Customer number' column, I do not manage to get the desired result (see (1) on the image above).

The desired result in this aggregated table would be:

YEAR	MONTH	WRONG Nb of cust 24 month	Desired Result
2018	March	3	2
2018	April	3	2
2018	May	3	2
2018	June	3	2
2018	July	3	1
2018	August	3	1
2018	September	3	1
2018	October	3	1
2018	November	3	1

Here is the code used for the aformentioned measures:

TEMP Nb cust 24 month = 
COUNTA(Customer[Customer Number])

Nb cust 24 month = 
IF(
    [Nb of months since last purchase] >= 0 && [Nb of months since last purchase] <= 24;
    CALCULATE(
        [TEMP Nb cust 24 month];
        FILTER(
            ALL(Calendar[Date]);
            Calendar[Date] <= MAX(Calendar[Date])
        )
    );
    BLANK()
)

TEST Nb cust 24 month = 
VAR Cust = 
[Nb cust 24 month]
RETURN
CALCULATE(
    COUNTA(Customer[Customer Number]);
    FILTER(
        Sales;
        Cust >0
    )
)

The data model is a simple star schema with a Fact Sales table and 2 dimension tables (Customer and Calendar, with the Calendar table being linked to the fact with 'Sales date').

Your help would be much appreciated.

Thanks in advance.

[v-juanli-msft · Accepted solution]

Hi @evolu6ion 

It is invaild of this measure.

TEST Nb cust 24 month = 
VAR Cust = 
[Nb cust 24 month]
RETURN
CALCULATE(
    COUNTA(Customer[Customer Number]);
    FILTER(
        Sales;
        Cust >0
    )
)

Since the table doesn't contain [Customer Number] column,don't use [Customer Number] column in this measure, try to use other column.

Or add [Customer Number] column in the table visual.

Best Regards
Maggie