Forum Discussion
Countif for specific customers
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
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
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.
Super UserHey,
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
Thanks a lot, it works perfectly :D !
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
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
Once again, thank you very much for your help ! It works perfectly :) !