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

AntrikshSharma's avatar
AntrikshSharma
Community Champion
3 years ago

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