Can't seem to figure this out. I have weekly (Friday) snapshot data on over 500 Projects with a Status field. My data is one .xlsx file that I keep concatenating to every week. My data spans all of 2019 to date (1/24/20).
There are over 15 statuses, but I am trying to count how many projects have switched to Complete status in 2020. There are currently 42 projects that have a Complete status, but most of them have become Complete in 2019.
How can I count when Status field changes to Complete (from any other status)? Should/can I do this at chart level, or in load script?
Sample data of one project: (altered for simplicity)
| PROJECT ID | DATE | STATUS | COMPLETION DATE (NEW FIELD) |
| AD231 | 11/29/2019 | Testing | - |
| AD231 | 12/6/2019 | Testing | - |
| AD231 | 12/13/2019 | Awaiting Confirmation | - |
AD231 | 12/20/2019 | Awaiting Confirmation | - |
| AD231 | 12/27/2019 | Awaiting Confirmation | - |
| AD231 | 1/3/2020 | Awaiting Confirmation | - |
| AD231 | 1/10/2020 | Complete | 1/10/2020 |
| AD231 | 1/17/2020 | Complete | - |
| AD231 | 1/24/2020 | Complete | - |
That project should be in my count, since it became Complete on 1/10/2020. This project should NOT be counted....
| PROJECT ID | DATE | STATUS | COMPLETION DATE (NEW FIELD) |
| AD188 | 11/29/2019 | Awaiting Confirmation | - |
| AD188 | 12/6/2019 | Awaiting Confirmation | - |
| AD188 | 12/13/2019 | Complete | 12/13/2019 |
| AD188 | 12/20/2019 | Complete | - |
| AD188 | 12/27/2019 | Complete | - |
| AD188 | 1/3/2020 | Complete | - |
| AD188 | 1/10/2020 | Complete | - |
| AD188 | 1/17/2020 | Complete | - |
| AD188 | 1/24/2020 | Complete | - |
.....Since it became Complete in 2019.
Thank you!!
