Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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?
Solved! Go to 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
Proud to be a Datanaut!
@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
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
Proud to be a Datanaut!
Many thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
52 | |
21 | |
12 | |
11 |