Calculate new customers per month using set analysis

[mirelaghenu]

Hello, 

I need to count those customers who bought nothing in the previous month, but in the current month their purchase is more than $200 (this is how I can consider them new in month). I couldn't identify yet the right way to write my set analysis, but this is the expression I used to identify my customers. 

Is there a way to achieve this using count? 

Thank you! 

IF(sum({<FLAG_S={'Invoices'}, Company={'ABC'}, DocumentDate={">=$(=Date(MonthStart(Max(DocumentDate)), 'YYYY-MM-DD'))<=$(=Date(Max(DocumentDate), 'YYYY-MM-DD'))"}>}Price*Qty)>200 and
sum({<FLAG_S={'Invoices'}, Company={'ABC'}, DocumentDate={">=$(=Date(AddMonths(MonthStart(Max(DocumentDate)),-1), 'YYYY-MM-DD'))<=$(=Date(AddMonths(MonthEnd(Max(DocumentDate)),-1), 'YYYY-MM-DD'))"}>}Price*Qty)=0, 1, 0)

[rubenmarin]

Hi, this may work if customer is the dimension of the table, at least row by row, it won't work on the totals row, in a KPI, or something else that doesn't has Customer as dimension.

To do the calculations simulating that there is a customer dimension you can add an Aggr() to make this calculation for each customer and do the count of the Aggr():

Count(Aggr([YourExpression], CustomerField))

[mirelaghenu]

Hi and thank you for your response! 
Sad to say I don not have Customer as a dimension in the pivot table. I tried to simulate it as you said, yet it does not seem to work. I need to count them by product group, product category and brand. Do you have any other ideas? Thank you again! 

[rubenmarin]

Hi, the aggr should work, if doesn't works I will need more feedback of what is not working, at least post the expression you are using to check if it's correct.

[mirelaghenu]

Sure, here is a sample of my report: Check column contains the If statement that I posted above. As you can see, between 1st and 5ft december there are at least 10 customers that I can consider as new. If I try the expression that you suggested, counting them by product group returns 0 new customers. What am I doing wrong? 

[rubenmarin]

Hi, I asked for the expression you are using on the 2nd table, to check if it's ok.

Another check you can do is to add the product group as dimension in the first table and check if it counts any customer. This is to check that product group has the needed relations in the data model.