Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
Let's say the table I desire to have looks more or less like this:
Having been given, for example, the register date and a customer, I want to see if the customer has been already registered in the last 3 months. Return "Existing" if so, "New" otherwise. How can I achieve that?
Register Date | Customer | IsNewCustomer |
12/01/2020 | A | New |
20/02/2020 | A | Existing |
22/03/2021 | B | New |
25/05/2021 | B | Existing |
11/04/2022 | B | New |
10/02/2020 | C | New |
24/03/2022 | C | New |
Solved! Go to Solution.
Hey @mba ,
I use the below DAX to compute a calculated column:
New Or Existing Customer =
var currentDate = 'Table'[Register Date]
var currentCustomer = 'Table'[Customer]
var NoOfMonth =
DATEDIFF(
MAXX(
TOPN(
1
, FILTER( 'Table' , 'Table'[Customer] = currentCustomer && 'Table'[Register Date] < currentDate )
, 'Table'[Register Date]
, DESC
)
, 'Table'[Register Date]
)
, currentDate
,MONTH
)
var result = NoOfMonth
return
IF( ISBLANK( result ) || result > 3 , "New" , "Existing" )
Based on the sample data you provided it looks like it creates the expected result:
Please check the condition that determines if it's a "New" or an "Exiting" customer, sometimes I have difficulties with this kind of condition.
Nevertheless, I hope this provides what you are looking for.
Regards,
Tom
Hey @mba ,
I use the below DAX to compute a calculated column:
New Or Existing Customer =
var currentDate = 'Table'[Register Date]
var currentCustomer = 'Table'[Customer]
var NoOfMonth =
DATEDIFF(
MAXX(
TOPN(
1
, FILTER( 'Table' , 'Table'[Customer] = currentCustomer && 'Table'[Register Date] < currentDate )
, 'Table'[Register Date]
, DESC
)
, 'Table'[Register Date]
)
, currentDate
,MONTH
)
var result = NoOfMonth
return
IF( ISBLANK( result ) || result > 3 , "New" , "Existing" )
Based on the sample data you provided it looks like it creates the expected result:
Please check the condition that determines if it's a "New" or an "Exiting" customer, sometimes I have difficulties with this kind of condition.
Nevertheless, I hope this provides what you are looking for.
Regards,
Tom
User | Count |
---|---|
93 | |
87 | |
77 | |
72 | |
66 |
User | Count |
---|---|
116 | |
107 | |
88 | |
65 | |
63 |