Counting records under multiple criteria in DAX

Question

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).

enter image description here

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

enter image description here

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, like below.

My desire result was a pivot table in below: enter image description here

I can do it by adding a calculated column in Customer table with below dax as filter to either 1 (the customer had reorder within 60days) or 0, then calculate at pivot table field:

IF(CALCULATE(COUNT(Sales[ProductKey]),FILTER(Sales,Sales[OrderDate]>Customers[DateFirstPurchase] && Sales[OrderDate] - Customers[DateFirstPurchase] <=60 && Sales[CustomerKey] = Customers[CustomerKey]))>0,1,0)

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 either:

1. Not using Filter function in calculated column ? i.e. check matching record from Customer table ? or other ways ? Or, ideally
2. Use Dax instead of adding calculated column.

It's highly appreciated for yours' help. Thanks.

Answer 1

If you have a relationship in a model then you don't need a Sales[CustomerKey] = Customers[CustomerKey]; Try to use this calculation (not tested).

MeasureToCount = CALCULATE (
    COUNTROWS (
        CALCULATETABLE (
             VALUES(Sales[CustomerKey]),
            FILTER (
                Sales,
                 Sales[SalesOrderDate]  > RELATED(Customer[FirstPurchase])
                    &&  (Sales[SalesOrderDate])  <= ( RELATED(Customer[FirstPurchase]) + 60 )
            )
        )
    )
)

Now if you add "Year & Month" to Table visualization with measure, that should work.

Output from powerbi Desktop: