New Customers each month - Purchased for the first time in 90 days

[gbarr12345]

Hi,

I'm looking to create a measure to show the new customers each month.

I would consider customers who have purchased for the first time in 90 days to be new customers so is there a measure out there that would be able to show customers who have purchased for the first time in 90 days?

I've attached links to my sample data below:

PBIX - https://drive.google.com/file/d/1MwFy4oJuBzzrmwJ7MPCSJkuMkFMU-xxl/view?usp=drive_link

Excel - https://docs.google.com/spreadsheets/d/1QZMFURunOHq7vHtHVVYAMHFTsYBrzESV/edit?usp=drive_link&ouid=11...

Many thanks in advance.

[adudani]

hi @gbarr12345 ,

create a blank query , copy and paste the code below into the advanced editor:

let
    Source = #"Sales Table",
    #"Grouped Rows" = Table.Group(Source, {"Customer ID"}, {{"MaxTransactionDate", each List.Max([Transaction Date]), type nullable date}, {"MinTransactionDate", each List.Min([Transaction Date]), type nullable date}, {"Data", each _, type table [Transaction ID=nullable number, Quantity Sold=nullable number, Market=nullable text, Customer ID=nullable number, Transaction Date=nullable date, Amount=nullable number, Item Code=nullable number, Country Label=nullable text, Period=nullable number]}, {"DistinctTransactionCount", each Table.RowCount(Table.Distinct(_)), Int64.Type}}),
    #"1st Transaction or not" = Table.AddColumn(#"Grouped Rows", "1st Transaction or not", each if [MaxTransactionDate] =[MinTransactionDate] then "1st Transaction" else "Not First Transaction", type text),
    #"Days from 1st Transaction" = Table.AddColumn(#"1st Transaction or not", "Days from 1st Transaction", each if [1st Transaction or not] = "1st Transaction" then Duration.Days(Date.From(DateTime.LocalNow())-[MinTransactionDate]) else null,Int64.Type),
    NewCustomerFlag = Table.AddColumn(#"Days from 1st Transaction", "NewCustomerFlag", each try  if [Days from 1st Transaction] <= 90 then "New Customer" else "Not a new customer" otherwise "Not a new customer")
in
    NewCustomerFlag

Output

if this doesn't resolve the issue, kindly elaborate/provide a sample output.

[gbarr12345]

Hi Avinash,

Thank you very much for your response.

Is there a DAX measure I could create instead instead of using advanced editor?

I'm looking to just show the customers who have purchased for the first time in 90 days or longer!

[adudani]

Created a calculated table using DAX finding new customers.
in a matrix visual, filtered the customer names using the flag.

let me know if this resolves the issue

[gbarr12345]

I created the 'Customers with first purchase in 90 days' table but am wondering what code you used to create the FirstPurchase in90Days' Table?

[adudani]

that was the initial PQ code provided 

[gbarr12345]

Ok thanks.

Is there a way to add to the calculated table code you provided below to also include the New Customer Flag column that you have from your PQ code as I can't use Power Query? 

Customerswithfirstpurchasein90daysormore =

FILTER(

ADDCOLUMNS(

ADDCOLUMNS(

SUMMARIZE(

    'Sales Table',

    'Sales Table'[Customer ID],

    "MinTransactionDate" , MIN('Sales Table'[Transaction Date]),

    "MaxTransactionDate" , MAX('Sales Table'[Transaction Date])

),

"1stTransactionornot", IF( [MinTransactionDate] = [MaxTransactionDate], "1st Transaction","NotthefirstTransaction")

),

"DaysFromFirstTransaction", INT( TODAY() - [MaxTransactionDate])

),

[DaysFromFirstTransaction]>= 90

)

[adudani]

Customerswithfirstpurchasein90daysormore =

ADDCOLUMNS(

ADDCOLUMNS(

ADDCOLUMNS(

SUMMARIZE(

    'Sales Table',

    'Sales Table'[Customer ID],

    "MinTransactionDate" , MIN('Sales Table'[Transaction Date]),

    "MaxTransactionDate" , MAX('Sales Table'[Transaction Date])

),

"1stTransactionornot", IF( [MinTransactionDate] = [MaxTransactionDate], "1st Transaction","NotthefirstTransaction")

),

"DaysFromFirstTransaction", INT( TODAY() - [MaxTransactionDate])

),

"NewCustomerorNot",

IF( [DaysFromFirstTransaction]>= 90, "new customer" , "not a new customer"))

[gbarr12345]

Getting this error and my relationships are in the screenshot attached. It's not far off though I'd say.

[adudani]

Removing old customers and any errors in the int if exists: 

Try:

Filter(

ADDCOLUMNS(

ADDCOLUMNS(

ADDCOLUMNS(

SUMMARIZE(

    'Sales Table',

    'Sales Table'[Customer ID],

    "MinTransactionDate" , MIN('Sales Table'[Transaction Date]),

    "MaxTransactionDate" , MAX('Sales Table'[Transaction Date])

),

"1stTransactionornot", IF( [MinTransactionDate] = [MaxTransactionDate], "1st Transaction","NotthefirstTransaction")

),

"DaysFromFirstTransaction", IFERROR(INT( TODAY() - [MinTransactionDate])

),0),

"NewCustomerorNot",

IF( [DaysFromFirstTransaction]>= 90, "new customer" , "not a new customer")

),

[NewCustomerorNot] = "new customer"

)

[gbarr12345]

Apologies, this is the error that is appearing for me:

[gbarr12345]

I tried that and it almost does what I want. 

However it's counting any customers who haven't purchased over 90 days as new customers where I want it to only show customers who have only purchased within the last 90 days to be classed as new customers:

[gbarr12345]

[adudani]

in line 19, change the greater than equal to sign to less than equal to 90?

[gbarr12345]

[adudani · Accepted solution]

Customerswithfirstpurchasein90daysormore = 
FILTER(
ADDCOLUMNS(
ADDCOLUMNS(
ADDCOLUMNS(
SUMMARIZE(
    'Sales Table',
    'Sales Table'[Customer ID],
    "MinTransactionDate" , MIN('Sales Table'[Transaction Date]),
    "MaxTransactionDate" , MAX('Sales Table'[Transaction Date])
),
"1stTransactionornot", 
IF( [MinTransactionDate] = [MaxTransactionDate], "1st Transaction","NotthefirstTransaction")
),
"DaysFromFirstTransaction", 
IFERROR(INT( TODAY() - [MaxTransactionDate]),0)
),
"NewCustomerornot",
IF([DaysFromFirstTransaction]<= 90, "new customer", "not a new customer")),
AND( [NewCustomerornot] ="new customer" , not ISBLANK([Customer ID]))
)

[gbarr12345]

Hi Avinash,

That worked thank you!

Thanks for all your help and hard work 🙂 

Really appreciate it!!