Counting records under multiple criteria in DAX

[twmy]

Hi All,

I have 2 tables.

A. Customer table. Key column include 1. Customer ID (primary key) and 2. First Date purchase (in Date field) and 3. First purchase Date Year & Month (in text, like Jul 01).

B. Sales table. Key column include 1. Customer ID (foreign key) and 2. Sales order date (in Date field).

There're multiple sales transaction for each customer first date purchase vary. Both tables was in 1-many relationship. I want to find out how many customer return within 60 days since their first purchase, group by First purchase Date Year & Month. For example,

• Jul-01 will have 2 return customer who return within 60 days since first purchase
• Aug-01 had no within 60 day return customer
• Sep-01 had 1 within 60 days return customer

I will then perform calculation in pivot table for % of return customer per first purchase month. My desire result was a pivot table in below:

I can do it by adding a dax in customer (dimension) table in below:

------

ReturnCustomer<=90days%:=DIVIDE(CALCULATE(COUNTROWS(CALCULATETABLE(VALUES(Sales[CustomerKey]),FILTER(Sales,Sales[OrderDate]>RELATED(Customers[DateFirstPurchase])&&Sales[OrderDate]<=RELATED(Customers[DateFirstPurchase])+90)))),[Nos. of Customer])

------

But as Sales had quite a large volumn, and it's not a best practice to use Filter in this case. Therefore, can this be achieve by Not Filter Sales in Dax? i.e. check matching record from Customer table ? or other ways ?

Thanks.

[twmy]

Hi Daxer,

Truly thanks a lot for your help and I have some question below:

1. Yes, I have a date table in the model indeed, just think the 2 tables were sufficient for the required information so didn't mention about it. Thanks again for alert. The Date table has relationship with Sales table with Date[Date] (in Date,Time field type) as primary key that link with Sales[SalesOrderDate].
2. I perform this in power pivot and had no isinscope function.
3. Summarize function not work as first argument was table, so the column Customer[CustomerKey] was alerted.

Please feel free for ask if I didn't explain clear. Thanks a lot for your help.

[Anonymous]

// Please be aware that this measure
// obeys all existing filters on any
// table. If you have a date table in
// the model, then this measure will
// have to be modified. But because you
// did not mention such a table, I assume
// it's not present in the model and
// write the DAX with this assumption
// in mind.

[Cust Return %] =
// You can adjust this parameter here
// or create a parameter table to harvest
// the number of days from it to be able
// to dynamically adjust this.
var ReturnWithinDays = 60
var Result =
    IF( ISINSCOPE( Customer[FPD_YearMonth] ),
        var AllVisibleCustsCount = COUNTROWS( Customer )
        var CustsReturnedCount =
            SUMX(
                SUMMARIZE(
                    Customer[CustomerKey],
                    Customer[FirstPurchaseDate]
                ),
                var FirstPurchaseDate =
                    Customer[FirstPurchaseDate]
                var LastDateConsidered =
                    FirstPurchaseDate + ReturnWithinDays                 
                return
                    calculate(
                        NOT ISEMPTY( Sales ),
                        Sales[SalesOrderDate] > FirstPurchaseDate,
                        Sales[SalesOrderDate] <= LastDateConsidered
                    )
            )
        var Ratio = 
            DIVIDE(
                CustsReturnedCount,
                AllVisibleCustsCount
            )
        return
            // This turns 0's into BLANKS.
            // If you want 0's, return Ratio
            // only without the IF.
            if( Ratio, Ratio )
    )
RETURN
    Result