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.
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
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |