Calculate count of last completed task only

[michal_rosa]

I have a table listing tasks per project. Each project has the same number of tasks and they can be either Completed or In Progress. The tasks don't have to be completed in order so it is possible for a task list to look for example like that:

Project 1 - 1 - Completed
Project 1 -2 - Completed
Project 1 -3 - In progress
Project 1 - 4 - Completed
Project 1 -5 - In progress
Project 2 - 1 - Completed
Project 2 - 2- In progress
Project 2 - 3- In progress
Project 2 - 4- In progress
Project 2 - 5- Completed
Project 3 - 1 - Completed
Project 3 - 2- In progress
Project 3 - 3- In progress
Project 3 - 4- In progress
Project 3 - 5- Completed


What I am trying to come up with, is a way to show the count of the last completed task. So if I was to chart the above list, it should show as:

1 - 0
2 - 0
3 - 0
4 - 1
5 - 2

Any help would be appreciated.

[Anonymous]

Hi @michal_rosa ,

Below is my table:

You can create a project_right to part the text:

Project_right = RIGHT('Table'[Project],1)

and then you can create a measure to achieve this:

Measure = CALCULATE(COUNT('Table'[flag]) , ALLEXCEPT('Table','Table'[Project_right]) , FILTER('Table','Table'[flag] = "Completed"))

The final output is shown in the following figure:

Best Regards,

Xianda Tang

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

[PhilipTreacy]

Hi @michal_rosa 

I'm guessing the example data you've provided isn't a true representation of your real data.

The reason I mention this is that if your Tasks are actual numbers, you can use MAX to work out the last completed task. 

But if the tasks have names (and are therefore text) you can't use MAX and would need some other way to work out the 'latest' completed task - like a date field.

Can you please post a sample of your actual data.

Regards

Phil