Forum Discussion
Solved
Custom Cycle Time
Hi, I have data like this: what I would like to achieve: user can select the start time and end time as they like , and the matrix gives the avg cycle time with carrier to vpc dril...
I found a solution with selectedvalue function. share it here in case someone may need.
~Custom Cycle Time (hr) =var date1=SELECTEDVALUE('~Stage Name_Start'[Stage Name_Start])var date2=SELECTEDVALUE('~Stage Name_End'[Stage Name_End])returnSWITCH(TRUE(),date1="1. Create Date" && date2="2. Pick Date",AVERAGEX('~Order for KPI',DATEDIFF('~Order for KPI'[create date],'~Order for KPI'[Pick Date],HOUR)),date1="1. Create Date" && date2="3. Ship Date",AVERAGEX('~Order for KPI',DATEDIFF('~Order for KPI'[create date],'~Order for KPI'[Ship Date],HOUR)),date1="1. Create Date" && date2="4. Delivery Date",AVERAGEX('~Order for KPI',DATEDIFF('~Order for KPI'[create date],'~Order for KPI'[~Deliver Date],HOUR)),date1="2. Pick Date" && date2="3. Ship Date",AVERAGEX('~Order for KPI',DATEDIFF('~Order for KPI'[Pick Date],'~Order for KPI'[Ship Date],HOUR)),date1="2. Pick Date" && date2="4. Delivery Date",AVERAGEX('~Order for KPI',DATEDIFF('~Order for KPI'[Pick Date],'~Order for KPI'[~Deliver Date],HOUR)),date1="3. Ship Date" && date2="4. Delivery Date",AVERAGEX('~Order for KPI',DATEDIFF('~Order for KPI'[Ship Date],'~Order for KPI'[~Deliver Date],HOUR)),"Please Select Valid Stage")
I found a solution with selectedvalue function. share it here in case someone may need.
~Custom Cycle Time (hr) =
var date1=SELECTEDVALUE('~Stage Name_Start'[Stage Name_Start])
var date2=SELECTEDVALUE('~Stage Name_End'[Stage Name_End])
return
SWITCH(TRUE(),
date1="1. Create Date" && date2="2. Pick Date",AVERAGEX('~Order for KPI',DATEDIFF('~Order for KPI'[create date],'~Order for KPI'[Pick Date],HOUR)),
date1="1. Create Date" && date2="3. Ship Date",AVERAGEX('~Order for KPI',DATEDIFF('~Order for KPI'[create date],'~Order for KPI'[Ship Date],HOUR)),
date1="1. Create Date" && date2="4. Delivery Date",AVERAGEX('~Order for KPI',DATEDIFF('~Order for KPI'[create date],'~Order for KPI'[~Deliver Date],HOUR)),
date1="2. Pick Date" && date2="3. Ship Date",AVERAGEX('~Order for KPI',DATEDIFF('~Order for KPI'[Pick Date],'~Order for KPI'[Ship Date],HOUR)),
date1="2. Pick Date" && date2="4. Delivery Date",AVERAGEX('~Order for KPI',DATEDIFF('~Order for KPI'[Pick Date],'~Order for KPI'[~Deliver Date],HOUR)),
date1="3. Ship Date" && date2="4. Delivery Date",AVERAGEX('~Order for KPI',DATEDIFF('~Order for KPI'[Ship Date],'~Order for KPI'[~Deliver Date],HOUR)),
"Please Select Valid Stage")