Forum Discussion

gton22's avatar
gton22
New Member
3 years ago
Solved

Add Custom Column based on calculated date ranges from current date

Hi   I have a column of dates called "Appointment Date" which references the last time we saw a client   I want to add a custom column "Client Status" which is generated by checking if the "Appoint...
Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    Hi gton22 ,

     

    Try this as a new custom column:

    clientStatus =
let Date.Today = Date.From(DateTime.LocalNow()) in
if [appointment date] >= Date.AddDays(Date.Today, -180) then "Active"
else if [appointment date] >= Date.AddDays(Date.Today, -270) then "Overdue"
else if [appointment date] >= Date.AddDays(Date.Today, -365) then "Lapsed"
else "Lost"

     

    Pete

BA_Pete's avatar
BA_Pete
Super User
3 years ago

Hi gton22 ,

 

Try this as a new custom column:

clientStatus =
let Date.Today = Date.From(DateTime.LocalNow()) in
if [appointment date] >= Date.AddDays(Date.Today, -180) then "Active"
else if [appointment date] >= Date.AddDays(Date.Today, -270) then "Overdue"
else if [appointment date] >= Date.AddDays(Date.Today, -365) then "Lapsed"
else "Lost"

 

Pete

gton22's avatar
gton22
New Member
3 years ago

Many thanks!