Create new column based on date

[SJHALANI]

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 Date	Contract Status
9/30/2022	Inactive
6/30/2023	Active
1/30/2023	Pending

Any help would be appreciated. Thanks!

[SJHALANI]

Thanks @Einomi This is working!

[Einomi · Accepted solution]

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