Forum Discussion

mmh's avatar
mmh
Icon for Helper I rankHelper I
3 years ago
Solved

New 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 ...
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    mmh
    Icon for Helper I rankHelper I
    3 years ago

    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_idvalid_fromValid_toNew Customer
    2021-1-1800 1-1-3000    Yes
    2021-1-20231-2-2024Yes

     

    Otherside some customer having record as like

    Customer_idvalidFromValidtoNew Customer ?
    1011-1-20201-2-2021Yes
    1012-1-20213-1-2022  NO 

     

  • mmh's avatar
    mmh
    Icon for Helper I rankHelper I
    3 years ago

    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_idvalid_fromValid_toNew Customer
    2021-1-1800 1-1-3000    Yes
    2021-1-20231-2-2024Yes

     

    Otherside some customer having record as like

    Customer_idvalidFromValidtoNew Customer ?
    1011-1-20201-2-2021Yes
    1012-1-20213-1-2022  NO 

     

    Thank You!

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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.