Forum Discussion

SJHALANI's avatar
SJHALANI
Helper I
3 years ago
Solved

Create new column based on date

Hello,

I have a column which has 'Contract End date' listed in it. I want to create a column 'Contract Status' next to it which does the following:

 

- if 'Contract End date' > current date, 'Contract Status' = Active

- if today - 60 days <'Contract End date' < today, 'Contract Status' = Pending

- if 'Contract End date' < today - 60 days, 'Contract Status' = Inactive

 

Contract End DateContract Status
9/30/2022Inactive
6/30/2023Active
1/30/2023Pending



Any help would be appreciated. Thanks!

 

Need Help
  • Einomi's avatar
    Einomi
    3 years ago

    Hi,

    You can try this

     

    Table.AddColumn(PrevStep, "ContractStatus", each if [Contract End Date] > Date.From(DateTime.LocalNow()) then "Active"
else if [Contract End Date] > (Date.AddDays(Date.From(DateTime.LocalNow()),-60)) and [Contract End Date] < Date.From(DateTime.LocalNow()) then "Pending"

else if [Contract End Date] < (Date.AddDays(Date.From(DateTime.LocalNow()),-60))  then "Inactive"
else null)

     

    I have tried on my data and it worked.

     

    Hope it will be helpful

2 Replies

  • Einomi's avatar
    Einomi
    Helper V
    3 years ago

    Hi,

    You can try this

     

    Table.AddColumn(PrevStep, "ContractStatus", each if [Contract End Date] > Date.From(DateTime.LocalNow()) then "Active"
else if [Contract End Date] > (Date.AddDays(Date.From(DateTime.LocalNow()),-60)) and [Contract End Date] < Date.From(DateTime.LocalNow()) then "Pending"

else if [Contract End Date] < (Date.AddDays(Date.From(DateTime.LocalNow()),-60))  then "Inactive"
else null)

     

    I have tried on my data and it worked.

     

    Hope it will be helpful