Progress Percentage Showing -105% (High Negative Percentage)

[kamini]

Hi folks,

I'm using the following code and it is working well except for the following record,

Start Date is showing 01/01/2025 and Due Date is showing 03/28/2025. 

But the following code is returning (-105%)

Progress = 
VAR DaysSinceStart = DATEDIFF([R_Start Date], TODAY(), DAY)
VAR TotalDuration = DATEDIFF([R_Start Date], 'ITD Projects'[Due Date], DAY)
VAR ProgressValue = DIVIDE(DaysSinceStart, TotalDuration, 0)
RETURN IF(TODAY() > 'ITD Projects'[Due Date], 1, ProgressValue)

Any ideas of what is happening and if any logic change can help to show just "0%" since the work has not started?

Thanks so much!

[Kedar_Pande · Accepted solution]

@kamini 

Add a condition to handle cases where the Start Date is in the future.

Modified DAX Code:

Progress =
VAR DaysSinceStart = DATEDIFF([R_Start Date], TODAY(), DAY)
VAR TotalDuration = DATEDIFF([R_Start Date], 'ITD Projects'[Due Date], DAY)
VAR ProgressValue = DIVIDE(MAX(DaysSinceStart, 0), TotalDuration, 0) -- Ensure DaysSinceStart is not negative
RETURN IF(TODAY() > 'ITD Projects'[Due Date], 1, ProgressValue)

If this helped, a Kudos 👍 or Solution mark would be great!
Cheers,
Kedar Pande
www.linkedin.com/in/kedar-pande

[rajendraongole1 · Accepted solution]

Hi @kamini - To avoid negative values and ensure that you show "0%" progress for projects that haven't started yet, you can add a condition to check whether the start date is in the future.

modified code FYR:

Progress =
VAR DaysSinceStart = DATEDIFF([R_Start Date], TODAY(), DAY)
VAR TotalDuration = DATEDIFF([R_Start Date], 'ITD Projects'[Due Date], DAY)
VAR ProgressValue = DIVIDE(DaysSinceStart, TotalDuration, 0)
RETURN
IF(
TODAY() < [R_Start Date],
0, -- Return 0% if today is before the start date
IF(TODAY() > 'ITD Projects'[Due Date],
1, -- Return 100% if today is past the due date
ProgressValue -- Else return the calculated progress
)
)

Hope the above calculation helps to avoid negative value. check and confirm

[Kedar_Pande · Accepted solution]

@kamini 

Add a condition to handle cases where the Start Date is in the future.

Modified DAX Code:

Progress =
VAR DaysSinceStart = DATEDIFF([R_Start Date], TODAY(), DAY)
VAR TotalDuration = DATEDIFF([R_Start Date], 'ITD Projects'[Due Date], DAY)
VAR ProgressValue = DIVIDE(MAX(DaysSinceStart, 0), TotalDuration, 0) -- Ensure DaysSinceStart is not negative
RETURN IF(TODAY() > 'ITD Projects'[Due Date], 1, ProgressValue)

If this helped, a Kudos 👍 or Solution mark would be great!
Cheers,
Kedar Pande
www.linkedin.com/in/kedar-pande