cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Employee

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

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

Thanks a lot!!

2 REPLIES 2
Employee

Hi @prab ,

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

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Employee

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))

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors