New Customers DAX

[Pinoo39]

Hi all,

I'm using "DaxPatterns"  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 work

VAR CustomersList =

CALCULATETABLE(VALUES(Sales[CustomerKey]), FILTER(Sales, Sales[type] in {7, 8} && Sales[status] in {4, 7}))

This is the Code :

Date New Customer :=

CALCULATE (                   -- The first sale is

    MIN ( Sales[Order Date] ), -- the MIN of the order date

    ALLEXCEPT (                 

        Sales,                -- ignoring any filter

        Sales[CustomerKey],   -- other than the customer

        Customer

    )

)

AND then number of New Customers is :

# New Customers :=

VAR CustomersWithNewDate =

    CALCULATETABLE (                       -- Prepares a table that

        ADDCOLUMNS (                       -- for each customer contains

            VALUES ( Sales[CustomerKey] ), -- the date of their first puchase

            "@NewCustomerDate", [Date New Customer]

        ),

        ALLEXCEPT ( Sales, Customer )

    )

VAR NewCustomers =

    FILTER (                              

        CustomersWithNewDate,              -- Filters the customers

        [@NewCustomerDate]                 -- whose new customer date

            IN VALUES ( 'Date'[Date] )     -- falls within the current period

    )

VAR Result =                               -- The count of the new customers

    COUNTROWS ( NewCustomers )             -- does not use the Sales table

RETURN

    Result

Any idea on doing that ?

[amitchandak]

@Pinoo39 , Try like, assuming a period is selected and the date table can give the dates for that period

This period =
var _min = minx(allselected('Date', 'Date'[Date]))
var _max = maxx(allselected('Date', 'Date'[Date]))
return
calculate([Sales], filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_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] <_min) )

New Customer This period = sumx(VALUES(Customer[Customer Id]), if(ISBLANK([Last period]) && not(ISBLANK([This period])) ,1,BLANK()))

[Pinoo39]

MINX and MAXX require two arguments

[amitchandak]

@Pinoo39 , what is your defination of new

[Pinoo39]

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

[amitchandak]

@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/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...

[Pinoo39]

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