New Customers DAX

Question

Hi all,&nbsp;I'm using "DaxPatterns"&nbsp; article to calculate New Customers but couldnt find a way to filter some customers first. I need to filter Customers List based on an attribute (e.g. "Status" column in Sales Table) then use it in the formula.I trid to define a variable like the following but it didn't workVAR CustomersList = CALCULATETABLE(VALUES(Sales[CustomerKey]), FILTER(Sales, Sales[type] in {7, 8} &amp;&amp; Sales[status] in {4, 7}))&nbsp;This is the Code :&nbsp;Date New Customer :=CALCULATE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- The first sale is&nbsp;&nbsp;&nbsp;&nbsp;MIN ( Sales[Order Date] ), -- the MIN of the order date&nbsp;&nbsp;&nbsp;&nbsp;ALLEXCEPT (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Sales,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- ignoring any filter&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Sales[CustomerKey],&nbsp;&nbsp; -- other than the customer&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Customer&nbsp;&nbsp;&nbsp;&nbsp;))&nbsp;AND then number of New Customers is :&nbsp;# New Customers :=VAR CustomersWithNewDate =&nbsp;&nbsp;&nbsp;&nbsp;CALCULATETABLE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Prepares a table that&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ADDCOLUMNS (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- for each customer contains&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VALUES ( Sales[CustomerKey] ), -- the date of their first puchase&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"@NewCustomerDate", [Date New Customer]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLEXCEPT ( Sales, Customer )&nbsp;&nbsp;&nbsp;&nbsp;)VAR NewCustomers =&nbsp;&nbsp;&nbsp;&nbsp;FILTER (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CustomersWithNewDate,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Filters the customers&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[@NewCustomerDate]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- whose new customer date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;IN VALUES ( 'Date'[Date] )&nbsp;&nbsp;&nbsp;&nbsp; -- falls within the current period&nbsp;&nbsp;&nbsp;&nbsp;)VAR Result =&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- The count of the new customers&nbsp;&nbsp;&nbsp;&nbsp;COUNTROWS ( NewCustomers )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- does not use the Sales tableRETURN&nbsp;&nbsp;&nbsp;&nbsp;ResultAny idea on doing that ?

amitchandak · Answer

Pinoo39 , refer if my two blogs can help. You need change the duration for new (Last period ) 
Customer Retention Part 1:https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458

amitchandak · Answer

Pinoo39 , Try like, assuming a period is selected and the date table can give the dates for that period 
&nbsp;
This period = var _min = minx(allselected('Date', 'Date'[Date]))var _max = maxx(allselected('Date', 'Date'[Date])) return calculate([Sales], filter('Date', 'Date'[Date] &gt;=_min &amp;&amp; 'Date'[Date] &lt;=_max) )
Last period = var _min = minx(allselected('Date', 'Date'[Date]))var _max = maxx(allselected('Date', 'Date'[Date])) return calculate([Sales], filter(all('Date'), 'Date'[Date] &lt;_min) )
&nbsp;
New Customer This period = sumx(VALUES(Customer[Customer Id]), if(ISBLANK([Last period]) &amp;&amp; not(ISBLANK([This period])) ,1,BLANK()))

pinoo39 · Answer

Since I'm using a DimDate Other than Gregorian DimDate, function like DatesMTD are not gonna work correctly

amitchandak · Answer

Pinoo39 , what is your defination of new

pinoo39 · Answer

A customer is considered new if He/She has bought an item in the selected period on slicer (e.g. This month) and not in the past

Forum Discussion

New Customers DAX

6 Replies

Related content

DAX help calculating Repeat Customers

Custom Reference Line Using DAX

Dax

DAX Formula Help

Custom Visual