Here’s a simple 3 column table showing Date, Project name (Cat.) and Status of the project. Each project can have multiple status entries on different dates. So as you can observe, project “alpha_9383993” was In Progress on Oct 2, 2017, remained so on October 5, 2017 but was completed on October 6, 2017.
| Date | Cat. | Status |
| 02-Oct-17 | alpha_9383993 | In Progress |
| 03-Oct-17 | Pulse_9387388 | In Progress |
| 04-Oct-17 | Pulse_9387388 | Rework |
| 05-Oct-17 | alpha_9383993 | In Progress |
| 06-Oct-17 | alpha_9383993 | Completed |
| 07-Oct-17 | Pulse_9387388 | Completed |
| 08-Oct-17 | Oppo_tes_9383 | In Progress |
| 09-Oct-17 | Oppo_Max_8977 | Rework |
The objective is to determine the count of projects by Status as per the most recent status of every project. So the expected result is:
| Row Labels | measure 2 |
| Completed | 2 |
| In Progress | 1 |
| Rework | 1 |
The result for In Progress should be one because there is only one such project – Oppo_tes_9383. Project alpha_9383993 should not be counted because it was completed on October 6, 2017. Likewise the result for Rework should be one because there is only one such project – Oppo_Max_8977. Project Pulse_9387388 should not be counted because it was completed on October 7,2017.
I have solved this problem with the PowerPivot. You may download my solution workbook from here.
Determine the total number of projects by Status
{ 0 Comments }