Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Many thanks in advance.
Solved! Go to 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]))
)
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.
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!
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
I created the 'Customers with first purchase in 90 days' table but am wondering what code you used to create the FirstPurchase in90Days' Table?
that was the initial PQ code provided
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 =
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"))
Getting this error and my relationships are in the screenshot attached. It's not far off though I'd say.
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"
)
Apologies, this is the error that is appearing for me:
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:
in line 19, change the greater than equal to sign to less than equal to 90?
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]))
)
Hi Avinash,
That worked thank you!
Thanks for all your help and hard work 🙂
Really appreciate it!!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
87 | |
80 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |