Filtering Without Filtering???

[littlemojopuppy]

I'd like to ask for some input on something.  

I recently had an encounter where I was asked to create DAX measures for the following:

1. 90 days of first purchase
   • Calculated per-customer according to each customer’s first purchase date
   • Literally, did they buy something during the 90 days immediately following their first purchase
   • So for this one, 90 days truly is an appropriate label, but it’s a different window per customer.
2. Following 3 months
   • If Jimmy’s first purchase was in July, I want to know whether he bought again in Aug, Sept, or Oct.
   • If he bought twice in July, that doesn’t count. We only look for whether he came back in Aug-Oct.
   • And his first purchase date doesn’t matter at all, except for the month. So July 1 first purchasers and July 31 first purchasers are exactly the same for this one.

I came up with the following measures:

Customers Returning Within 90 Days of First Purchase:=COUNTROWS(
		FILTER(
			Customers,
			CALCULATE(
				DISTINCTCOUNT(Sales[SalesOrderNumber]),
				FILTER(
					ALL('Calendar'),
					Calendar[Date] < MAX('Calendar'[Date])
				)
			) > 1 &&
                 		DATEDIFF(
				Customers[DateFirstPurchase],
				CALCULATE(
					MIN(Sales[OrderDate]),
					Sales[CustomerKey] = EARLIER(Customers[CustomerKey]),
					Sales[OrderDate] > EARLIER(Customers[DateFirstPurchase])
				),
				DAY
			) <= 90
                 	)
	)

Customers Returning Within Following Three Months:=COUNTROWS(
		FILTER(
			Customers,
			CALCULATE(
				DISTINCTCOUNT(Sales[SalesOrderNumber]),
				FILTER(
					ALL(Calendar),
					Calendar[Date] >= EOMONTH(EARLIER(Customers[DateFirstPurchase]),0) + 1  &&
					Calendar[Date] <= EOMONTH(EARLIER(Customers[DateFirstPurchase]) ,3)
				)
			)
		)
	)

Further on in the discussion, I was asked to achieve the exact same result for the measure "Customers Returning Within 90 Days of First Purchase" without filtering the Sales table at all.  I will completely admit that I am stumped on how to accomplish this.  Could anyone please provide some guidance on how to this can be done?

[amitchandak]

Just check a rank that can be created on the date within the customer. That will give you the order and based on that you can calculate your next or previous date.

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

[littlemojopuppy]

Hi -

Thanks for replying.  I'm not getting it.  The biggest thing I'm not understanding is how would you limit sales transactions to only those of a specified customer without filtering?

Secondarily and far less important, if I rank sales transactions based on date, the first one would be ranked 1, the second one would be 2, etc.  Whether the second one happened within 90 days won't be reflected in a ranking...

I'm confused!  🙂

[littlemojopuppy · Accepted solution]

So I am not one to give up on things so last weekend I sat down and figured this out:

Customers Returning Within 90 Days of First Purchase Redux:=CALCULATE(
		[Customer Count],
		FILTER(
			Customers,
			CALCULATE(
				DISTINCTCOUNT(Sales[SalesOrderNumber]),
				FILTER(
					ALL(Calendar),
					Calendar[Date] > EARLIER(Customers[DateFirstPurchase])  &&
					Calendar[Date] <= EARLIER(Customers[DateFirstPurchase]) + 90
				)
			)
		)
	)

Customers Returning Within Following Three Months Redux:=CALCULATE(
		[Customer Count],
		FILTER(
			Customers,
			CALCULATE(
				DISTINCTCOUNT(Sales[SalesOrderNumber]),
				FILTER(
					ALL(Calendar),
					Calendar[Date] >= EOMONTH(EARLIER(Customers[DateFirstPurchase]),0) + 1  &&
					Calendar[Date] <= EOMONTH(EARLIER(Customers[DateFirstPurchase]) ,3)
				)
			)
		)
	)

My original solution for Customers Returning Within 90 Days of First Purchase clearly demonstrates that while there are lots of ways to solve something but not all of them are good.  This solution is simple and straightforward.

[thatnerdguy]

Did this solution work for you?

[littlemojopuppy]

@thatnerdguy I would approach this in a completely different way now than I did four years ago.

Good luck with the interview! 😉