Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
kamini
Helper III
Helper III

Progress Percentage Showing -105% (High Negative Percentage)

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. 

kamini_0-1727972018686.png

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!

2 ACCEPTED SOLUTIONS
Kedar_Pande
Memorable Member
Memorable Member

@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

View solution in original post

rajendraongole1
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

2 REPLIES 2
rajendraongole1
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Kedar_Pande
Memorable Member
Memorable Member

@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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.