Using DateTime.LocalNow IF Else

[dferry]

I am trying to use today's date to subtract from a column in power query that is a Due Date.  There could be a null value in the Due Date column.

My formula is if Date.From(DateTime.LocalNow()) - [DueDate] <=0 then "OnTime" else Date.From(DateTime.LocalNow()) - [DueDate]

It says No Syntax Errors have been detected but the results in the column are errors.  The error says Expression.Error: We cannot apply operator < to types Number and Duration.
Details:
Operator=<
Left=0
Right=847.00:00:00

[BA_Pete · Accepted solution]

Hi @dferry ,

Try this instead:

if [DueDate] = null then null
else if Date.From(DateTime.LocalNow()) < [DueDate] then "OnTime"
else Text.From(
    Number.From(
        Duration.Days(Date.From(DateTime.LocalNow()) - [DueDate])
    )
)

You may need to fiddle with the final escape clause to get the exact output format you want, but the first two conditions should solve your isue.

Pete

[BA_Pete · Accepted solution]

Hi @dferry ,

Try this instead:

if [DueDate] = null then null
else if Date.From(DateTime.LocalNow()) < [DueDate] then "OnTime"
else Text.From(
    Number.From(
        Duration.Days(Date.From(DateTime.LocalNow()) - [DueDate])
    )
)

You may need to fiddle with the final escape clause to get the exact output format you want, but the first two conditions should solve your isue.

Pete

[dferry · Accepted solution]

Kudos Pete.  That worked.  Thank you very much.

Doug