topic New Customer column in DAX Commands and Tips

New Customer column

mmh — Tue, 17 Jan 2023 14:15:59 GMT

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

Re: New Customer column

amitchandak — Tue, 17 Jan 2023 15:14:24 GMT

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")

Re: New Customer column

FreemanZ — Tue, 17 Jan 2023 15:32:29 GMT

hi mmh

can you explain your YES/NO logic?

Re: New Customer column

Anonymous — Wed, 18 Jan 2023 05:30:50 GMT

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.

Re: New Customer column

mmh — Wed, 18 Jan 2023 09:29:03 GMT

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

Re: New Customer column

mmh — Wed, 18 Jan 2023 09:29:54 GMT

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!

Re: New Customer column

mmh — Wed, 18 Jan 2023 09:32:18 GMT

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

Re: New Customer column

Anonymous — Wed, 18 Jan 2023 09:48:03 GMT

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.