Countif for specific customers

[Anonymous]

Hello,

I have searched for a solution of my problem for hours but could'nt find any satisfying one.

I want to "translate" this Excel formula in DAX, knowing that all the information are in the same table (SALES). The final objective is to make a SUM of the column G in order to know how many purchases are made by customers who have made their first purchase during the current year.

Thanks for your help !

[TomMartens · Accepted solution]

Hey,

it seems this measure

NoOfPurchasesInFirstYear = 
CALCULATE(
SUMX(
	'Purchases'
	,var customer = 'Purchases'[Customer]
	var yearoffirstpurchase = YEAR('Purchases'[PurchaseDate])
	return
		COUNTROWS(
			FILTER(
				ALL('Purchases')
				,Purchases[Customer] = customer 
				&& YEAR('Purchases'[PurchaseDate]) = yearoffirstpurchase
			)
		)
	)
,'Purchases'[IsFirstPurchase] = "Yes"
)

creates what you are looking for

Here is sample data if you want to recreate my example

PurchaseDate	Customer	IsFirstPurchase
2017-01-07	C1	No
2017-07-09	C1	No
2016-08-02	C1	No
2016-06-01	C1	Yes
2017-01-07	C2	Yes
2017-07-09	C2	No
2016-07-04	C1	No

Hope this is what you are looking for

[TomMartens]

Hm,

wondering how the Excel formula would work, to me it seems that it would count also the first purchase  in a previous year. Because there is no check in the formula considering the purchase date.

My understanding

• all purchases of one customer in its first year would be flagged as TRUE
• You want to count the number of Purchases from new customer and not the customer

Can you please provide feedback if my understanding is correct?

Regards

[Anonymous]

Actually you're right about the fact that it counts all purchases without consideration of the year (I thought I'll tackle this problem later on :)). If I could have all the following purchases (count of it) from one customer whose 1st purchase is in the filtered year, it would already be great success for my calculation.

In my Excel sheet, as long as a customer has made his first purchase (labelled as "true"), all the following purchases he makes are not labelled as "true" which means my COUNTIF will give me only one value (the number of all purchases following a first purchase, plus this first purchase) for these specific customers with first purchase.

Hope this helps for your understanding.

Thanks for your help.

[TomMartens · Accepted solution]

Hey,

it seems this measure

NoOfPurchasesInFirstYear = 
CALCULATE(
SUMX(
	'Purchases'
	,var customer = 'Purchases'[Customer]
	var yearoffirstpurchase = YEAR('Purchases'[PurchaseDate])
	return
		COUNTROWS(
			FILTER(
				ALL('Purchases')
				,Purchases[Customer] = customer 
				&& YEAR('Purchases'[PurchaseDate]) = yearoffirstpurchase
			)
		)
	)
,'Purchases'[IsFirstPurchase] = "Yes"
)

creates what you are looking for

Here is sample data if you want to recreate my example

PurchaseDate	Customer	IsFirstPurchase
2017-01-07	C1	No
2017-07-09	C1	No
2016-08-02	C1	No
2016-06-01	C1	Yes
2017-01-07	C2	Yes
2017-07-09	C2	No
2016-07-04	C1	No

Hope this is what you are looking for

[Anonymous]

I wonder if you can help me further:

I'd like to make the sum of the total amount (from Purchased value) paid by these customers whose first purchase was made this current year. The sum is the total amount paid by these specific customers for the filtered year, not only the sum of their first purchase.

Once again thank you for your help and I wish you a great weekend.

[TomMartens · Accepted solution]

Be my guest.

The result

The Measure

SumOfPurchasesInFirstYear = 
CALCULATE(
SUMX(
	'Purchases'
	,var customer = 'Purchases'[Customer]
	var yearoffirstpurchase = YEAR('Purchases'[PurchaseDate])
	return
		CALCULATE(SUM(Purchases[Amount]),
			FILTER(
				ALL('Purchases')
				,Purchases[Customer] = customer 
				&& YEAR('Purchases'[PurchaseDate]) = yearoffirstpurchase
			)
		)
	)
,'Purchases'[IsFirstPurchase] = "Yes"
)

The enhanced sample data (sligthly enhanced :-))

PurchaseDate	Customer	IsFirstPurchase	Amount
2017-01-07	C1	No	1
2017-07-09	C1	No	2
2016-08-02	C1	No	3
2016-06-01	C1	Yes	4
2017-01-07	C2	Yes	5
2017-07-09	C2	No	6
2016-07-04	C1	No	7

Cheers

[Anonymous]

Once again, thank you very much for your help ! It works perfectly 🙂 !

[Anonymous]

Thanks a lot, it works perfectly 😄 !