Very High Percentage is showing for Progress in my Power BI Report

[kamini]

Hi foks,

I have a formula as shown below and for some reason is showing me a 7050% of Progress in my dashboard. Start Date 4/24/2024 and finish date 04/26/2024. 

Progress = divide(datediff([start Date], today(), day), datediff([start date], [Finish Date], day))

Below is the column setting,

and what is displayed under the Table,

Any ideas what's going on?

Thank you in advance for your time.

[Shravan133 · Accepted solution]

Hi,

Whats your expected output? 100%??

The issue is likely caused by how the DATEDIFF function calculates the difference and how TODAY() compares with the Finish Date in your formula. If TODAY() is well past the Finish Date (e.g., in September 2024 while your project ended in April), the numerator in your formula will be much larger than the denominator, causing the percentage to be unreasonably high.

1. DATEDIFF([Start Date], TODAY(), DAY): This calculates the number of days between the Start Date and today’s date (let's say today is 9/13/2024). So, from 4/24/2024 to 9/13/2024, this is 142 days.

2. DATEDIFF([Start Date], [Finish Date], DAY): This calculates the number of days between the Start Date and the Finish Date (4/24/2024 to 4/26/2024), which is only 2 days.

Now, you're dividing 142 days (since the start date) by 2 days (duration of the task), resulting in an inflated percentage:

Progress = DIVIDE(142, 2) = 71 (or 7100%).

Try this:  Cap Progress at 100%

You need to ensure that Progress does not exceed 100% by limiting the numerator to the total task duration if TODAY() is past the Finish Date.

Here’s an updated version of the formula to handle this:

Progress =

VAR DaysSinceStart = DATEDIFF([Start Date], TODAY(), DAY)

VAR TotalDuration = DATEDIFF([Start Date], [Finish Date], DAY)

VAR ProgressValue = DIVIDE(DaysSinceStart, TotalDuration, 0)

RETURN IF(TODAY() > [Finish Date], 1, ProgressValue)

[lbendlin · Accepted solution]

Finish Date is in the past. 

Progress = divide(datediff([start Date], today(), day), datediff([start date], max([Finish Date],TODAY()), day))

[Shravan133 · Accepted solution]

Hi,

Whats your expected output? 100%??

The issue is likely caused by how the DATEDIFF function calculates the difference and how TODAY() compares with the Finish Date in your formula. If TODAY() is well past the Finish Date (e.g., in September 2024 while your project ended in April), the numerator in your formula will be much larger than the denominator, causing the percentage to be unreasonably high.

1. DATEDIFF([Start Date], TODAY(), DAY): This calculates the number of days between the Start Date and today’s date (let's say today is 9/13/2024). So, from 4/24/2024 to 9/13/2024, this is 142 days.

2. DATEDIFF([Start Date], [Finish Date], DAY): This calculates the number of days between the Start Date and the Finish Date (4/24/2024 to 4/26/2024), which is only 2 days.

Now, you're dividing 142 days (since the start date) by 2 days (duration of the task), resulting in an inflated percentage:

Progress = DIVIDE(142, 2) = 71 (or 7100%).

Try this:  Cap Progress at 100%

You need to ensure that Progress does not exceed 100% by limiting the numerator to the total task duration if TODAY() is past the Finish Date.

Here’s an updated version of the formula to handle this:

Progress =

VAR DaysSinceStart = DATEDIFF([Start Date], TODAY(), DAY)

VAR TotalDuration = DATEDIFF([Start Date], [Finish Date], DAY)

VAR ProgressValue = DIVIDE(DaysSinceStart, TotalDuration, 0)

RETURN IF(TODAY() > [Finish Date], 1, ProgressValue)

[kamini]

Thank you for the great explanations.