Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
gton22
New Member

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 "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?

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

3 REPLIES 3
AntrikshSharma
Super User
Super User

@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

AntrikshSharma_0-1670421418886.png

BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Many thanks!

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.