cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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

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: Measures.png

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

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

Hi @gcv1999 ,

 

qq5.PNG

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]
)

qq6.PNG

 

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.

 

 

View solution in original post

4 REPLIES 4
Highlighted
Community Support
Community Support

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

Hi @gcv1999 ,

 

Please refer to my .pbix file.

vvv5.PNG

 

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.

Highlighted
Helper I
Helper I

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

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

 

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?

Highlighted
Community Support
Community Support

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

Hi @gcv1999 ,

 

qq5.PNG

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]
)

qq6.PNG

 

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.

 

 

View solution in original post

Highlighted
Community Support
Community Support

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

Hi @gcv1999 ,

 

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors