Filter DistinctCount calculation to same Customer IDs found in other calculation, same cohort

[Grygger]

I'm trying to identify the percentage of customers who purchased something in the last 1 month (based on the x axis) and compare vs. the previous 90 day period and filter down the two calculations to be of the same cohort.

What I'm struggling to do is filter down my previous 90 day query to contain ONLY the same CustomerIDs that were found purchasing in the last 30 days.

i.e. if we take a date, say July 1st, _Last1M_Customers will take a distinctcount of CustomerIDs between June 1st - July 1st. The _Last3M_Customers will look at March 1st - May 31st to get the previous 90 day period.

VAR _Last1M_Customers = CALCULATE(DISTINCTCOUNT(sales[CustomerID]), DATESINPERIOD(sales[Sale_Date], MIN(sales[Sale_Date]), -30, DAY))

VAR _Last3M_Customers = CALCULATE(DISTINCTCOUNT(sales[CustomerID]), DATESINPERIOD(sales[Sale_Date], DATEADD(FIRSTDATE(sales[Sale_Date]), -31, DAY), -91, DAY))

RETURN _Last1M_Customers/_Last3M_Customers

I'm more comfortable with SQL and what I'm essentially trying to do is add logic to the _Last3M_Customers that is something akin to

WHERE CustomerID IN (SELECT CustomerID FROM _Last1M_Customers)

[Anonymous]

@Grygger Maybe this helps:

VAR _Last1M_Customers = CALCULATE(DISTINCTCOUNT(sales[CustomerID]), DATESINPERIOD(sales[Sale_Date], MIN(sales[Sale_Date]), -30, DAY))

VAR _Last1M_Customers_List=SUMMARIZE(FILTER(sales,DATESINPERIOD(sales[Sale_Date], MIN(sales[Sale_Date]), -30, DAY)),sales[CustomerID])

VAR _Last3M_Customers = COUNTROWS(SUMMARIZE(FILTER(sales,DATESINPERIOD(sales[Sale_Date], DATEADD(FIRSTDATE(sales[Sale_Date]), -31, DAY), -91, DAY)&&_Last1M_Customers_List),sales[CustomerID]))

RETURN _Last1M_Customers/_Last3M_Customer

[Grygger]

This definitely gets one step closer! My only challenge is that there can be duplicate customer IDs for multiple purchases, so that's why I've kept the DistinctCount() function throughout, I could be wrong but would this inflate the count with duplicate customer IDs?

[Anonymous]

DistinctCount counts unique cutomer ID, without duplicates. If you need count  unique [customer ID]&[purchase ID] then modify DAX a little bit:

VAR _Last1M_Customers = COUNTROWS(SUMMARIZE(FILTER(sales,DATESINPERIOD(sales[Sale_Date], MIN(sales[Sale_Date]), -30, DAY)),sales[CustomerID],sales[PurchaseID]))

VAR _Last1M_Customers_List=SUMMARIZE(FILTER(sales,DATESINPERIOD(sales[Sale_Date], MIN(sales[Sale_Date]), -30, DAY)),sales[CustomerID],sales[PurchaseID])

VAR _Last3M_Customers = COUNTROWS(SUMMARIZE(FILTER(sales,DATESINPERIOD(sales[Sale_Date], DATEADD(FIRSTDATE(sales[Sale_Date]), -31, DAY), -91, DAY)&&_Last1M_Customers_List),sales[CustomerID],sales[PurchaseID]))

RETURN _Last1M_Customers/_Last3M_Customer

[Grygger]

No luck unfortunately, comes back with an error along the lines of expected a single value but received multiple values.