Project Task Status History / Calculating data for Flow Metrics reporting.
Is there a table in the database that stores the history of Project Tasks? I am looking for a way to pull the dates that a status changes for Project Tasks in order to build a Cumulative Flow Diagram for Agile / Scrum metrics in Power Bi. I've been able to pull other data and create a Scrum Sprint Dashboard showing Story Points completed in a Sprint, WIP, and Burndown. Now I am working on some of the other metrics like Cycle Times, Cumulative Flow,Throughput, and Velocity.
Answer (1)
Hi Darrin,
The only place this is sort of stored is the database ItemUpdates table. First in the feed text. There are IU_PercentCompleteOld and IU_PercentCompleteNew columns.
There are IU_StatusIDOld and IU_StatusIDNew columns too, but I'm not certain for project tasks that it ever fully records a "status change" since Tasks don't use statuses in quite the same sense as tickets.
We have an internal KB that states the following regarding statuses in the database:
Status and Percent Complete Values
The IU_StatusIDOld and IU_StatusIDNew columns can contain old/new status values for the following types of items:
| Type | Component ID | Referenced Table |
|---|---|---|
| Project | 1 | Statuses |
| Issue | 3 | Statuses |
| Asset | 27 | AssetStatuses |
Task/Card is not one such item.