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!!
Hi @prab ,
The Cycletime, P50, and P90 aren't columns. So how can we calculate them?
Best Regards,
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..
User | Count |
---|---|
106 | |
88 | |
69 | |
52 | |
49 |
User | Count |
---|---|
148 | |
94 | |
79 | |
71 | |
70 |