Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Im trying to count distinct new customers based on their order timing. My goal is to create a line graph that will show total customer count (im good here) in relation to new customer count on a monthly basis.
A new customer is a customer that ordered in the current month and has not ordered in the prior 2 months
Based on the sample data set:
March = 1 new customer
April = 1 new cx
May = 2 new cx
June = 0 new cx
July = 2 new cx (Cust 3 last ordered in May so does not qualify; Cust 2 and Cust 1 last ordered in April so they do qualify)
I apologize for my posting etiquette. I am new to this community but excited to learn. Thanks in advance!
Solved! Go to Solution.
@Anonymous
Give this MEASURE a shot as well
Measure = VAR CurrentMonthCustomer = VALUES ( Table1[Customer] ) VAR Prior2MonthsStart = EOMONTH ( MAX ( Table1[Order Date] ), -3 ) + 1 VAR Prior2MonthsEnd = EOMONTH ( Prior2MonthsStart, 1 ) VAR Prior2MonthCsutomers = CALCULATETABLE ( VALUES ( Table1[Customer] ), FILTER ( ALL ( Table1 ), Table1[Order Date] >= Prior2MonthsStart && Table1[Order Date] <= Prior2MonthsEnd ) ) RETURN COUNTROWS ( EXCEPT ( CurrentMonthCustomer, Prior2MonthCsutomers ) )
Hi,
You may refer to my solution in this PBI file.
Hope this helps.
Assuming you have a DateTable and are using Month from the Date table for your chart axix.
Number of Orders = DISTINCTCOUNT(OrderID) Number of Orders Prior 2 Months = CALCULATE([Number of Orders],DATESINPERIOD(Date[Date],MIN(Date[Date])-1,-2,,MONTH))
You can then use [=Number of Orders Prior 2 Months] to test if its a New Customer or not.
@Anonymous
Give this MEASURE a shot as well
Measure = VAR CurrentMonthCustomer = VALUES ( Table1[Customer] ) VAR Prior2MonthsStart = EOMONTH ( MAX ( Table1[Order Date] ), -3 ) + 1 VAR Prior2MonthsEnd = EOMONTH ( Prior2MonthsStart, 1 ) VAR Prior2MonthCsutomers = CALCULATETABLE ( VALUES ( Table1[Customer] ), FILTER ( ALL ( Table1 ), Table1[Order Date] >= Prior2MonthsStart && Table1[Order Date] <= Prior2MonthsEnd ) ) RETURN COUNTROWS ( EXCEPT ( CurrentMonthCustomer, Prior2MonthCsutomers ) )
How would I adjust this measure if I want to change the parameters to no sales in the past 3 months instead of 2? Thank you
@Zubair_Muhammad This worked! Where did you learn how to do this? I would greatly appreciate if you could point me in the right direction. Thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |