Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
prab
Employee
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. 

RequestIdmilestoneStartdateEnddateMONTHCycletimeP50P90
111milestone12/2/20162/22/2016    
111milestone22/22/20163/22/2016    
111milestone33/22/20164/25/2016    
111milestone44/26/2016     
111milestone5NULL5/11/2017    
111milestone65/12/20175/17/2017    
111milestone85/17/20178/4/2017    
111milestone98/4/20179/20/2017    
111milestone109/20/2017NULL    
111milestone11NULLNULL    
222milestone11/22/20162/22/2016    
222milestone22/22/20163/22/2016    
222milestone33/22/20165/4/2016    
222milestone45/4/201612/20/2016    
222milestone512/20/20167/14/2017    
222milestone67/15/20179/26/2017    
222milestone79/27/20179/29/2017    
222milestone89/29/201712/20/2017 294294294
222milestone912/20/20171/10/2018    
222milestone101/10/20185/15/2018    
222milestone115/15/20187/20/20187/1/2018   
333milestone1NULL1/23/2018    
333milestone21/23/20182/14/2018    
333milestone32/14/2018NULL    
333milestone4NULLNULL    
333milestone5NULL4/4/2018    
333milestone6NULL4/5/2018    
333milestone84/5/20189/14/2018 229178.9427083219.1113194
333milestone99/14/201811/17/2018    
333milestone1011/17/201811/20/2018    
333milestone1111/20/201811/20/201811/1/2018   
444milestone1NULL5/10/2018    
444milestone25/10/20185/24/2018    
444milestone35/24/2018NULL    
444milestone4NULLNULL    
444milestone5NULL6/12/2018    
444milestone66/26/20186/28/2018    
444milestone86/28/20189/26/2018 129  
444milestone99/26/201810/1/2018    
444milestone1010/1/201810/3/2018    
444milestone1110/3/201811/3/2018    
555milestone1NULL5/10/2018    
555milestone25/10/20184/11/2018    
555milestone34/11/2018NULL    
555milestone4NULLNULL    
555milestone5NULL5/31/2018    
555milestone67/10/20187/13/2018    
555milestone87/13/201810/1/2018    
555milestone910/1/20181/29/2019    
555milestone101/29/2019NULL    
555milestone11NULLNULL    

 

List of all Milestone.

MilestoneOrder of milestone
milestone11
milestone22
milestone33
milestone44
milestone55
milestone66
milestone77
milestone88
milestone99
milestone1010
milestone1111

 

 

Request your help!!

Thanks a lot!!

2 REPLIES 2
v-jiascu-msft
Employee
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.

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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