Dynamic calculation of Cycle time of 2 or more selected milestone from slicer

[prab]

Hi 

Need some help or let me know if this possible or not

We need to find the cycletime between 2 or more selected milestone and generate 50 & 90 percentile for the month of the end date of the requests in the powerbi. 

The percentile are calculated based on the cycletime between the selected milestones.

for example,

if user selects 1 milestone from the slicer (milestone8), the cycletime need to be calculated for the RequestID between minimum startdate and max enddate

if user selects 2 milestones (milestone8 & milestone11), the cycletime need to be calculated for the RequestID between minimum startdate (i.e., startdate of milestone8) and max enddate (enddate milestone11).

if user selects 3 milestones (milestone5, milestone8 & milestone11), the cycletime need to be calculated for the RequestID between minimum startdate (i.e., startdate of milestone8) and max enddate (enddate milestone11).

Below is the sample data.

Table to capture the start and end date of each milestone

Cycletime, P50 and P90  are not colums, these need to be calculate. 

RequestId	milestone	Startdate	Enddate	MONTH	Cycletime	P50	P90
111	milestone1	2/2/2016	2/22/2016
111	milestone2	2/22/2016	3/22/2016
111	milestone3	3/22/2016	4/25/2016
111	milestone4	4/26/2016
111	milestone5	NULL	5/11/2017
111	milestone6	5/12/2017	5/17/2017
111	milestone8	5/17/2017	8/4/2017
111	milestone9	8/4/2017	9/20/2017
111	milestone10	9/20/2017	NULL
111	milestone11	NULL	NULL
222	milestone1	1/22/2016	2/22/2016
222	milestone2	2/22/2016	3/22/2016
222	milestone3	3/22/2016	5/4/2016
222	milestone4	5/4/2016	12/20/2016
222	milestone5	12/20/2016	7/14/2017
222	milestone6	7/15/2017	9/26/2017
222	milestone7	9/27/2017	9/29/2017
222	milestone8	9/29/2017	12/20/2017		294	294	294
222	milestone9	12/20/2017	1/10/2018
222	milestone10	1/10/2018	5/15/2018
222	milestone11	5/15/2018	7/20/2018	7/1/2018
333	milestone1	NULL	1/23/2018
333	milestone2	1/23/2018	2/14/2018
333	milestone3	2/14/2018	NULL
333	milestone4	NULL	NULL
333	milestone5	NULL	4/4/2018
333	milestone6	NULL	4/5/2018
333	milestone8	4/5/2018	9/14/2018		229	178.9427083	219.1113194
333	milestone9	9/14/2018	11/17/2018
333	milestone10	11/17/2018	11/20/2018
333	milestone11	11/20/2018	11/20/2018	11/1/2018
444	milestone1	NULL	5/10/2018
444	milestone2	5/10/2018	5/24/2018
444	milestone3	5/24/2018	NULL
444	milestone4	NULL	NULL
444	milestone5	NULL	6/12/2018
444	milestone6	6/26/2018	6/28/2018
444	milestone8	6/28/2018	9/26/2018		129
444	milestone9	9/26/2018	10/1/2018
444	milestone10	10/1/2018	10/3/2018
444	milestone11	10/3/2018	11/3/2018
555	milestone1	NULL	5/10/2018
555	milestone2	5/10/2018	4/11/2018
555	milestone3	4/11/2018	NULL
555	milestone4	NULL	NULL
555	milestone5	NULL	5/31/2018
555	milestone6	7/10/2018	7/13/2018
555	milestone8	7/13/2018	10/1/2018
555	milestone9	10/1/2018	1/29/2019
555	milestone10	1/29/2019	NULL
555	milestone11	NULL	NULL

List of all Milestone.

Milestone	Order of milestone
milestone1	1
milestone2	2
milestone3	3
milestone4	4
milestone5	5
milestone6	6
milestone7	7
milestone8	8
milestone9	9
milestone10	10
milestone11	11

Request your help!!

Thanks a lot!!

[v-jiascu-msft]

Hi @prab ,

The Cycletime, P50, and P90 aren't columns. So how can we calculate them?

Best Regards,

[prab]

The calculation of Cycletime, P50 & P90 are as below, but the calculation should based on the milestone selected by user in the slicer. These can be created as measure..

Cycletime = Min(startdate) of first milestone  - Max(enddate) of the last milestone 

P90 = CALCULATE( PERCENTILE.INC(CT_Data[CYCLETIME],0.9))
P50 = CALCULATE( PERCENTILE.INC(CT_Data[CYCLETIME],0.5))

Hope this clarifies your query..