Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |