# of new customers per month with Power Pivot

[charleyb0y]

Hi,

I'm fairly new to Power Pivot and I've been playing around with Calculated columns, measures and DAX.

I have a data set that contains all orders from all customers, with the date associated. I want to display the new customers only, per month. Outside of Power Pivot, I would probably have used COUNTIF to check for other orders from same customer in previous months.... I've seen numerous posts about "countif with DAX", but I never seem to be able to translate the examples to my specific issue.

For example, looking at this pivot table, I see that Customer A and D were new customers in January, and that customer B and C were new customers in February.

Would someone be able to guide me in the right direction? I have spent hours looking at measures, and then at calculated columns... I just don't seem to get how I would need to achieve this.

Thanks for your help

 

[charleyb0y]

I got it to work using a mix of a calculated column and measures.

Calculated column:

=IF(CALCULATE(COUNTROWS(Data),FILTER(ALLEXCEPT(Data,Data[Customer Code]),Data[Date (end of period)]< EARLIER(Data[Date (end of period)])))> 1,false,true)

Measures:

Total Customers:=DISTINCTCOUNT(Data[Customer Code])
New Customers:=CALCULATE(DISTINCTCOUNT([Customer Code]),Data[New customer]=true)
Returning Customers:=CALCULATE(DISTINCTCOUNT([Customer Code]),Data[New customer]=false)