Hi
I am sruggling with the consept of our new data warehouse team and approach. The legacy warehouse used to produce me a daily snapshot report that pointed to our outstanding repairs table which looked at all open repairs and whether they were awaiting scheduling or were scheduled. This provided me insight into working progress overtime.
Our new warehouse contains an Dimension outstanding repairs table that appplies a new row if the status has changed and has a start and end date of these changes.
Where as the Snapshot would have continuous daily added rows for a repair for the duration that it remained outstanding
The Dimention table only has an entry (row) if the status changes from awaiting scheduling or scheduled
This means the Dimention table looks more like this. Jobs with dates spread out based on status changes
| Job# | Dim Date | Status |
| 1 | 01/01/2024 | Awaiting Shed |
| 1 | 04/01/2024 | Scheduled |
| 1 | 18/01/2024 | Awaiting Shed |
| 1 | 19/01/2024 | Scheduled |
| 2 | 01/01/2024 | Scheduled |
| 2 | 18/01/2024 | Awaiting Shed |
| 2 | 19/01/2024 | Scheduled |
| 3 | 01/01/2024 | Scheduled |
| 3 | 18/01/2024 | Awaiting Shed |
| 3 | 12/02/2024 | Scheduled |
| 4 | 01/01/2024 | Awaiting Shed |
| 4 | 03/02/2024 | Scheduled |
| 5 | 01/01/2024 | Awaiting Shed |
| 5 | 06/01/2024 | Scheduled |
| 5 | 13/01/2024 | Awaiting Shed |
| 5 | 21/01/2024 | Scheduled |
| 5 | 25/01/2024 | Awaiting Shed |
| 5 | 30/01/2024 | Scheduled |
As opposed to the snapshot table which looked like this. An entry even if the status did not change
| Job# | Date Added to snapshot | Status |
| 1 | 01/01/2024 | Awaiting Shed |
| 1 | 02/01/2024 | Awaiting Shed |
| 1 | 03/01/2024 | Awaiting Shed |
| 1 | 04/01/2024 | Scheduled |
| 1 | 05/01/2024 | Scheduled |
| 1 | 06/01/2024 | Scheduled |
| 1 | 07/01/2024 | Scheduled |
| 1 | 08/01/2024 | Scheduled |
| 1 | 09/01/2024 | Scheduled |
| 1 | 10/01/2024 | Scheduled |
| 1 | 11/01/2024 | Scheduled |
| 1 | 12/01/2024 | Scheduled |
| 1 | 13/01/2024 | Scheduled |
| 1 | 14/01/2024 | Scheduled |
| 1 | 15/01/2024 | Scheduled |
| 1 | 16/01/2024 | Scheduled |
| 1 | 17/01/2024 | Scheduled |
| 1 | 18/01/2024 | Awaiting Shed |
| 1 | 19/01/2024 | Awaiting Shed |
| 1 | 20/01/2024 | Awaiting Shed |
| 1 | 21/01/2024 | Awaiting Shed |
| 1 | 22/01/2024 | Awaiting Shed |
| 1 | 23/01/2024 | Awaiting Shed |
| 1 | 24/01/2024 | Awaiting Shed |
| 1 | 25/01/2024 | Awaiting Shed |
| 1 | 26/01/2024 | Awaiting Shed |
| 1 | 27/01/2024 | Awaiting Shed |
My question is that I need to chart the outstanding repairs over time using a line chart. With the snapshot table I was able to do this fine as the dates were continuous.
However now that IT have introduced a Dimention table that has entries only when the status changes it creates lots of oddspikes in the line chart as the dates are not continuous. Just to add , I do use a dates table.
Should I do one of the following
1- Ask the data warehouse team to start build a table to produces a snap shot of the outstanding repairs table
2-Or is there a DAX function that would solve the problem. We will be running the DAX over 250K -1 Million rows.
thank you Richard
