I need to track the change in status from week to week and within a week, by item and team. I don't know where to even start.
PBIX file
Example 1:
Team Banana was assigned Item 1026281 last week. This week, they changed the status three times.
| Team | ItemID | Status of Event | Occurred |
| Banana | 1026281 | Not Started | 5/29/2020 |
| Banana | 1026281 | Ready | 6/1/2020 |
| Banana | 1026281 | In Progress | 6/2/2020 |
| Banana | 1026281 | Approved | 6/3/2020 |
Example 2:
Team Pear was assigned Item 115987 this week and moved it to Approved.
| Team | ItemID | Status of Event | Occurred |
| Pear | 115987 | Not Started | 6/1/2020 |
| Pear | 115987 | Approved | 6/2/2020 |
Example 3:
A bunch of teams were assigned a bunch of items this week that they haven't started.
| Team | ItemID | Status of Event | Occurred |
| Apple | 1020661 | Not Started | 6/2/2020 |
| Orange | 27070 | Not Started | 6/2/2020 |
| Orange | 593452 | Not Started | 6/2/2020 |
| Pomegranite | 600839 | Not Started | 6/2/2020 |
| Banana | 606239 | Not Started | 6/2/2020 |
| Apple | 71623 | Not Started | 6/2/2020 |
| Pear | 611534 | Not Started | 6/1/2020 |
I want to see all of this movement, both from last week and within this week.
Key things to note:
I need to call out the Items that have a status of Not Started but no other status. If they have Not Started and another status, that means they had work done and need to be counted separately.
Status changes can be forward or backward (from Approved back to In Progress), and they can skip steps (from Not Started to Approved). Not every item starts with Not Started.
What I'm aiming for is something like this:
Week Ending June 5
Apple:
Not Started (2)
Banana:
Not Started (1)
Items with status changes (1)
Orange:
Not Started (2)
Pear:
Not Started (1)
Items with status changes (1)
Pomegranite:
Not Started (2)
Total By Status at the end of the week:
Not Started (7), down from 18 at end of last week, a 60% decrease
Ready (0), no change from end of last week
In Progress (0), no change from end of last week
Approved (1), down from 2 at end of last week, a 50% decrease
There's a huge amount to pull apart here, but I can't figure out how to tease out the status changes.
