Add Custom Column based on calculated date ranges from current date

[gton22]

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 "Appointment Date" falls into the following thresholds and returns the following values:

1. if [appointment date] is within the last 180 days then [Client Status] = "Active"
2. if [appointment date] is between 181 days and 270 days from the current date, then [Client Status] = "Overdue
3. if [appointment date] is between 271 days and 365 days from the current date, then [Client Status] = "Lapsed"
4. else [Client Status] = "Lost"

I know I need to use an IF boolean statement and incorporate the variable

DateTime.LocalNow()

but I am unable to structure the code.  Please can someone kindly advise?

[AntrikshSharma]

@gton22 You can create a configuration table either in between the same query or a different query, paste this code in the advanced editor:

let
    Source = 
        Table.FromRows (
            Json.Document (
                Binary.Decompress (
                    Binary.FromText (
                        "i45WMjIwMtI1NNQ1sFCK1YFyDUx1DQxhXENdQwOwbCwA",
                        BinaryEncoding.Base64
                    ),
                    Compression.Deflate
                )
            ),
            let
                _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
            in
                type table [ #"Appointment Date" = _t ]
        ),
    ChangedType = Table.TransformColumnTypes ( Source, { { "Appointment Date", type date } } ),
    StatusTable = 
        #table (
            type table [ Min = Int64.Type, Max = Int64.Type, Status = text ],
            {
                { 0, 180, "Active" },
                { 180, 270, "OverDue" },
                { 270, 365, "Lapsed" },
                { 365, 9999999, "Lost" }
            }
        ),
    AddedCustom = 
        Table.AddColumn (
            ChangedType,
            "Status",
            each
                let
                    DaysElapsed = 
                        Duration.Days (
                            Date.From ( DateTime.LocalNow() ) - [#"Appointment Date"]
                        ),
                    Status = 
                        Table.SelectRows (
                            StatusTable,
                            each DaysElapsed >= [Min] and DaysElapsed < [Max]
                        )[Status]{0}
                in
                    Status,
            type text
        )
in
    AddedCustom

[BA_Pete · Accepted solution]

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]

Many thanks!