Need help adding status to dates

[swaggerboy69]

Hello guys!

I need help in adding status to dates.

For example the screenshot below, if it's beyond the due date, it will show as Overdue in the Status column. If it's 5 days before the due date, it'll say Almost due and if it's beyond 5 days before the due date, it'll say On time.

[swaggerboy69]

Can anyone help me with this please?

[amitchandak]

@swaggerboy69 , Try a new column like

new column =
var _diff = datediff([Date], today(),day)
return
switch(true(),
_diff<0 , "Overdue",
_diff<5 , "Almost Due",
"On Time"
)

[swaggerboy69]

There's inaccuracy of the data using that query.

I'm seeing October 28 as Almost due. It should be Overdue.

Also seeing October 31 as On Time. It should be Almost Due.

Additionally, if the Due date is blank, is there a way that it will retain as blank in the status?

Currently, it's saying Almost complete on blank Due dates.

[v-shex-msft]

HI @swaggerboy69,

Your table seems stored random dates with irregular value fields. Any available category and index use to filter records and find out the previous/next date based on current row contents?

In addition, it seems like your task code contains some numerical value that can work as index.
You can try to extract it and use it to find out the previous or next record, then you can calculate the diff between these and use if statement to mark and return tags.

Regards,

Xiaoxin Sheng

[swaggerboy69]

I was able to get it to work using the Query below:

Due Status =

var _diff = datediff(today(), 'CR_Dashboard'[Due date],day)

return

switch(true(),

_diff<0 , "Overdue",

_diff<5 , "Almost Due",

"On time"

)

However, my problem is that it shows as 'Almost Due' for fields with no date.

Can anyone help out?

[v-shex-msft · Accepted solution]

Hi @swaggerboy69,

What type of result that you want to return if the current date is blank? Perhaps you can try this one and replace the 'N/A' with the result you wanted:

Due Status =
VAR _diff =
    DATEDIFF ( TODAY (), 'CR_Dashboard'[Due date], DAY )
RETURN
    IF (
        'CR_Dashboard'[Due date] <> BLANK (),
        IF ( _diff < 0, "Overdue", IF ( _diff < 5, "Almost Due", "On time" ) ),
        "N/A"
    )

Regards,

Xiaoxin Sheng