New customers from sales/transaction data

[unclejemima]

I'm trying to generate a report for 'new' customers in a month.

I found a good tutorial online...but I'm having trouble making it work.

The code they suggest looks like this...

New customers = 

VAR currentCustomers = VALUES('Sales table'[customer])
VAR currentDate = MIN('Sales table'[date])

VAR pastCustomers = CALCULATETABLE(VALUES('Sales table'[customer]), 
    ALL('Sales table'[date].[Month],'Sales table'[date].[MonthNo],'Sales table'[date].[Year])
    , 'Sales table'[date]<currentDate)

VAR newCustomers = EXCEPT(currentCustomers,pastCustomers)

RETURN COUNTROWS(newCustomers)

I'm doing this as a 'new measure'.

First issue I have is the with the VAR.  As I input this and it only has option for VAR.P, VAR.S  NOT for just VAR.  Is VAR a valid statement to call?

Secondly, when I get down to ALL('Sales table'[date].[Month] it is ok with Sales table[date] but as soon as I type .[Month] it will show red as an error.  

The sample table is VERY simple...looks like this...

Here is a link to the full tutorial I was working from...

https://finance-bi.com/blog/power-bi-new-and-repeat-customers/

[v-yalanwu-msft · Accepted solution]

Hi, @unclejemima ,

Try the measure.

New customers = 
VAR currentCustomers =SUMMARIZE(FILTER(ALL('Table'),EOMONTH([date],0)=EOMONTH(MAX('Table'[date]),0)),[customer])

VAR pastCustomers = SUMMARIZE(FILTER(ALL('Table'),EOMONTH([date],0)<EOMONTH(MAX('Table'[date]),0)),[customer])

VAR newCustomers = EXCEPT(currentCustomers,pastCustomers)

RETURN COUNTROWS(newCustomers)

The final show:

If you want to get sum of new custom.

New customers  sales = 

VAR currentCustomers =SUMMARIZE(FILTER(ALL('Table'),EOMONTH([date],0)=EOMONTH(MAX('Table'[date]),0)),[customer])

VAR pastCustomers = SUMMARIZE(FILTER(ALL('Table'),EOMONTH([date],0)<EOMONTH(MAX('Table'[date]),0)),[customer])

VAR newCustomers = EXCEPT(currentCustomers,pastCustomers)

RETURN SUMX(newCustomers,CALCULATE(SUM([Sales])))

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson]

VAR is not a function like VAR.P that calculates variation. It's a bit of code that tells DAX that you're defining a new variable. More detail here and here.

The .[month] and .[year] suffixes are related to automatic time intelligence and not really a good idea to use, in my personal opinion. It's generally better to have a proper date table.

I'd recommend checking out these resources related to customer retention:

https://www.daxpatterns.com/new-and-returning-customers

https://radacad.com/customer-retention-in-power-bi-dax-measures

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.