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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gcv1999
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

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
v-lionel-msft
Community Support
Community Support

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.

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?

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.

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.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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