Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Countif for specific customers

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 !

 

Need Help
  • TomMartens's avatar
    TomMartens
    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

  • TomMartens's avatar
    TomMartens
    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 :-))

    PurchaseDateCustomerIsFirstPurchaseAmount
    2017-01-07C1No1
    2017-07-09C1No2
    2016-08-02C1No3
    2016-06-01C1Yes4
    2017-01-07C2Yes5
    2017-07-09C2No6
    2016-07-04C1No7

     

    Cheers

7 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        TomMartens
        Icon for Super User rankSuper User
        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