Forum Discussion
Countif for specific customers
- 9 years ago
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
- 9 years ago
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
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.
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
- Anonymous9 years agoNot applicable
Once again, thank you very much for your help ! It works perfectly :) !