Forum Discussion
How do I create a dynamic slicer for calculating varying cycle times
I have a project that contains multiple stages where a date is recorded, I want to calculate the cycle times between the stages.
As the project contains over 10 stages I want to make it easy for the end user to select the starting stage and the ending stage and have the report calculate the cycle time rather than pre-calculating all possible cycles in the data transformation.
Example data
| Item Line No. | Stage_1 | Stage_2 | Stage_3 | Stage_4 | Stage_5 | Stage_6 | Stage_7 |
| 3180373 | 14/09/2020 | 14/09/2020 | 26/10/2020 | 14/09/2020 | 14/09/2020 | 16/10/2020 | 18/01/2021 |
| 3180374 | 14/09/2020 | 21/09/2020 | 28/10/2020 | 14/09/2020 | 25/09/2020 | 29/10/2020 | 16/09/2020 |
| 3180376 | 14/09/2020 | 14/09/2020 | 23/09/2020 | 14/09/2020 | 17/09/2020 | 22/10/2020 | 29/12/2020 |
| 3180378 | 14/09/2020 | 15/09/2020 | 28/09/2020 | 14/09/2020 | 15/09/2020 | 17/09/2020 | 13/10/2020 |
| 3180380 | 14/09/2020 | 16/09/2020 | 30/11/2020 | 14/09/2020 | 16/11/2020 | 11/12/2020 | 23/10/2020 |
Example end-user selection
Selection Start: Stage_2
Selection End: Stage_7
Output: Average Cycle time
Example end-user selection
Selection Start: Stage_3
Selection End: Stage_5
Output: Average Cycle time
First step would be to unpivot the data to bring it into usable format. Note that your sample dates are out of sequence (later stages have earlier dates) - please correct as needed.
see attached
2 Replies
- lbendlin
Super User
First step would be to unpivot the data to bring it into usable format. Note that your sample dates are out of sequence (later stages have earlier dates) - please correct as needed.
see attached