Calculate Task Completion and Display the message "Completed".

[bix]

Above screen shot. Created new column with ..... 

Days Left or OverDue = 'XXX'[Actual Due Date]-TODAY() .....

to calculate the days left or overdue.

But some tasks already completed. Instead of showing days (-100) or (-9) for completed tasks, I want to show up for the completed tasks (Completed), as of completion % showing 100%. 

Also, I want to show up for over due, if tasks not completed within time duration, show up (9 Days Overdue).

[v-jiascu-msft · Accepted solution]

Hi @bix,

Since the [Completion %] doesn't have a total value, please try the formula below.

Days Left or OverDue =
VAR days =
    DATEDIFF ( MIN ( [Actual Due Date] ), TODAY (), DAY )
RETURN
    IF (
        ISBLANK ( [Completion %] ),
        BLANK (),
        IF (
            [Completion %] = 1,
            "Completed",
            IF ( days > 0, days & " Days Overdue", - days & " Left" )
        )
    )

Best Regards,

Dale

[v-jiascu-msft]

Hi @bix,

Try this formula please. I assume the [Completion %] is a measure.

Days Left or OverDue =
VAR days =
    DATEDIFF ( MIN ( [Actual Due Date] ), TODAY (), DAY )
RETURN
    IF (
        [Completion %] = 1,
        "Completed",
        IF ( days > 0, days & " Days Overdue", -days & " Left" )
    )

Best Regards,

Dale

[bix]

Thanks, Almost Done. 

1. But not able to remove the last line of total, as showing 101 days overdue. 

2. if completion % is blank, how i can keep "Blank", as it is showing in first row (Left).

[v-jiascu-msft · Accepted solution]

Hi @bix,

Since the [Completion %] doesn't have a total value, please try the formula below.

Days Left or OverDue =
VAR days =
    DATEDIFF ( MIN ( [Actual Due Date] ), TODAY (), DAY )
RETURN
    IF (
        ISBLANK ( [Completion %] ),
        BLANK (),
        IF (
            [Completion %] = 1,
            "Completed",
            IF ( days > 0, days & " Days Overdue", - days & " Left" )
        )
    )

Best Regards,

Dale

[bix]

Hi Dale,

Thank you so much, solution is working fine. Except the total, I tried to hide the total number of days as showing 101-Days Overdue. Do you have any solution to hide the last Row of Total. 

Regards,

Bix.

[AdamBoltryk · Accepted solution]

Hi, Isn't your total just standard PBI's total? you can turn it off here: