topic Re: New Customers DAX in DAX Commands and Tips

New Customers DAX

Pinoo39 — Thu, 03 Dec 2020 07:56:52 GMT

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 ?

Re: New Customers DAX

amitchandak — Thu, 03 Dec 2020 07:55:17 GMT

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/1361529
Customer 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

Re: New Customers DAX

Pinoo39 — Thu, 03 Dec 2020 08:07:24 GMT

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

Re: New Customers DAX

amitchandak — Thu, 03 Dec 2020 09:05:15 GMT

Pinoo39 , what is your defination of new

Re: New Customers DAX

Pinoo39 — Thu, 03 Dec 2020 09:17:25 GMT

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

Re: New Customers DAX

amitchandak — Thu, 03 Dec 2020 12:05:32 GMT

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()))

Re: New Customers DAX

Pinoo39 — Thu, 03 Dec 2020 12:53:12 GMT

MINX and MAXX require two arguments