Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Calculate Task Completion and Display the message "Completed".

 

 

 

 

 

 

 

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).

 

 

 

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi Anonymous,

     

    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

  • AdamBoltryk's avatar
    AdamBoltryk
    8 years ago

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

5 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Anonymous,

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi Anonymous,

         

        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

Related content