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
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!
Solved! Go to Solution.
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
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
Proud to be a 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
Proud to be a Super User! | |
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
99 | |
94 | |
88 | |
70 |
User | Count |
---|---|
166 | |
131 | |
128 | |
102 | |
98 |