How do I create a dynamic slicer for calculating varying cycle times

[Anonymous]

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

[lbendlin · Accepted solution]

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

[Anonymous]

Thank you Ibendlin,

I tried your solution and it worked perfectly