Count Overdue items only if goal is not met and only up to the goal

[Anonymous]

Hello Everyone,

I received help a few days ago which you can view here: https://community.powerbi.com/t5/Desktop/Sum-and-Total-IDs-up-to-a-given-goal/m-p/1166627#M526822

I'd like to use the formulas that were provided in that thread and tweak them for other problems I am facing. For a reference here are the measures that were created: 

Using the above formulas as a base, I'd like to count the number of overdue items up to a set goal. Please look at the following spreadsheet for specific values: Spreadsheet Link 

Basically, there is a set goal for each taskID. There is also a targetCompletion date and percentComplete for each taskID and ID. If a goal for a taskID is 4 and there are 4 IDs that have completed the taskID, then there are 0 overdue items no matter if there are extra past the goal. If there is a goal of 3 for a taskID and 2 are completed, but there are 3 others that are assigned and their targetCompletion is past today, there is just 1 overdue taskID because only 1 is needed to reach the goal.

Thank you, and let me know if this needs to be explained further.

[v-lionel-msft]

Hi @Anonymous ,

Please refer to my .pbix file.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you for the reply @v-lionel-msft. Your measures work up until the _Count measure. 

The MAX function does not make sense in this context because I'd like to compare each taskID's number of completed to their respective goal. Is there something I could do where each number of complete is compared to the taskID's respective goal. For instance, if the goal for a taskID is 2 (where 2 is not the max in the goal column) and the completed tasks is 1, it would return 2-1 instead of MAX(goal) - 1?

[v-lionel-msft]

Hi @Anonymous ,

Has your problem been solved?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

I calculated it as you said.

The MAX() function here returns the value of the current row, not the maximum value.

You can also use  SELECTEDVALUE() function.

_Count = 
IF(
    [Number completed] >= SELECTEDVALUE(Table2[goal]),
    0,
    SELECTEDVALUE(Table2[goal])-[Number completed]
)

In addition, projects with [targetCompletionDate] greater than today are also considered completed.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.