New Customer column

[mmh]

Hi, 

I have a table with repeated and new customers, some new customers  having as initialized date as (1800-1-1 til 1-1-3000)  

Customer_id	valid_from	Valid_to
1	1-1-2020	1-2-2021
1	2-1-2021	3-1-2022
2	1-1-1800	1-1-3000
2	1-1-2023	1-2-2024

I  would like to have a calculated column or custom column  for new customer as follows:

Customer_id	validFrom	Validto	New Customer ?
1	1-1-2020	1-2-2021	Yes
1	2-1-2021	3-1-2022	NO
2	1-1-1800	1-1-3000	Yes
2	1-1-2023	1-2-2024	Yes

[Anonymous]

Hi @mmh 

You can refer to the following example:

New Customer = IF(MONTH('Table'[valid_from])=1&&DAY([valid_from])=1,"Yes","No")

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mmh]

HI Yolo,

Thanks! But its not correct.  Actually here some new customer having two records as like
Intials date n approved date , so it would be identified as new customer  in both records

Customer_id	valid_from	Valid_to	New Customer
202	1-1-1800	1-1-3000	Yes
202	1-1-2023	1-2-2024	Yes

Otherside some customer having record as like

Customer_id	validFrom	Validto	New Customer ?
101	1-1-2020	1-2-2021	Yes
101	2-1-2021	3-1-2022	NO

Thank You!

[Anonymous · Accepted solution]

Hi @mmh 

You can try the following code:

New Customer = var _filter=FILTER('Table',[Customer_id]=EARLIER('Table'[Customer_id]))
var _indate=MINX(_filter,[valid_from])
var _end=MAXX(FILTER(_filter,[valid_from]=_indate),[Valid_to])
return IF([valid_from]>=_indate&&[Valid_to]<=_end,"Yes","No")

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mmh]

HI Yolo,

Thanks! But its not correct.  Actually here some new customer having two records as like
Intials date n approved date , so it would be identified as new customer  in both records

Customer_id	valid_from	Valid_to	New Customer
202	1-1-1800	1-1-3000	Yes
202	1-1-2023	1-2-2024	Yes

Otherside some customer having record as like

Customer_id	validFrom	Validto	New Customer ?
101	1-1-2020	1-2-2021	Yes
101	2-1-2021	3-1-2022	NO

[FreemanZ]

hi @mmh 

can you explain your YES/NO logic?

[mmh]

HI Yolo,

Thanks! But its not correct.  Actually here some new customer having two records as like
Intials date n approved date , so it would be identified as new customer  in both records

Customer_id	valid_from	Valid_to	New Customer
202	1-1-1800	1-1-3000	Yes  (its intial date)
202	1-1-2023	1-2-2024	Yes  (its approval date)

Otherside some customer having record as like

Customer_id	validFrom	Validto	New Customer ?
101	1-1-2020	1-2-2021	Yes
101	2-1-2021	3-1-2022	NO

[amitchandak]

@mmh ,

new column =

var _min = minx(filter(Table, [Customer_id] = earlier([Customer_id])  && [validFrom] < earlier([validFrom]) ), [validFrom])

return

if(_min  = date(1800,01,01) || isblank(_min), "Yes", "No")