Forum Discussion
Helper INew Customer column
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 |
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.
7 Replies
- amitchandak
Super User
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")
mmhHI 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 recordsCustomer_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
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 recordsCustomer_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 - 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 recordsCustomer_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!
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.
