Custom Column based on Date in another column

[karmaimages]

Hi

I have a list of SLA due dates in a column called "SLA"

I need to create a new column based on those dates which will create a text value in the new column based on the following:

if SLA date = date in past and not equal to today then value = Over Due
If SLA date = due today or next 30 days then value = SLA Due within 30 days
if SLA date = >30 days then value = within SLA

Is this possible to do within PowerBi?

 

[RoryA]

Yes. DateTime.LocalNow() returns the current date and time so: convert that to just a date, then it's a series of if statements. For example:

=let today = Date.From(DateTime.LocalNow()) in if [SLA Date] < today then "Over Due" else if [SLA Date] < Date.AddDays(today, 30) then "Due today or next 30 days" else "within SLA"

 

[karmaimages]

=let today = Date.From(DateTime.LocalNow()) in if [SLA Date] < today then "Over Due" else if [SLA Date] < Date.AddDays(today, 30) then "Due today or next 30 days" else "within SLA"

That worked perfectly, thank you very much

 

[karmaimages]

Yes. DateTime.LocalNow() returns the current date and time so: convert that to just a date, then it's a series of if statements. For example:

=let today = Date.From(DateTime.LocalNow()) in if [SLA Date] < today then "Over Due" else if [SLA Date] < Date.AddDays(today, 30) then "Due today or next 30 days" else "within SLA"

One query on this, if I have a blank date within [SLA Date] can I get it to ignore this rather than create an error or enter something like "Data Missing"

 

[RoryA]

You'll need to handle that separately first with another if:

if [SLA Date] = null then "whatever" else ...

 

[karmaimages]

You'll need to handle that separately first with another if:

if [SLA Date] = null then "whatever" else ...

I tried:

= Table.AddColumn(#"Changed Type3", "In/Out of SLA", each let today = Date.From(DateTime.LocalNow()) in if [SLA] < today then "Over Due" else if [SLA] < Date.AddDays(today, 30) then "Due today/Next 30 days" else if [SLA] = null then "Information Missing" else "Within SLA")

But this is still throwing an error:

Expression.Error: We cannot convert the value null to type Logical.
Details:
Value=
Type=[Type]

 

[RoryA]

You need the null test to be the first one.

 

[karmaimages]

You need the null test to be the first one.

Thank you Rory, that worked