Custom Cycle Time

[Wendy100]

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

I know it is crazy but that where I am . Is it possible?
Much apprciated.

[Wendy100 · Accepted solution]

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

[Anonymous]

Hi  @Wendy100 

You can consider to use field paramater, you can refer to the following sample.

Sample data 

1.Create two paramater tables

Paramater 1

Paramater 2 is the sama as paramater 1

2.Create a measure

Measure =
VAR _start =
    SWITCH (
        MAX ( Parameter[Parameter Order] ),
        0, MAX ( 'Table'[Deliver Date] ),
        1, MAX ( 'Table'[Order Date] ),
        2, MAX ( 'Table'[Pick Date] ),
        3, MAX ( 'Table'[Ship Date] )
    )
VAR _end =
    SWITCH (
        MAX ( 'Parameter 2'[Parameter Order] ),
        0, MAX ( 'Table'[Deliver Date] ),
        1, MAX ( 'Table'[Order Date] ),
        2, MAX ( 'Table'[Pick Date] ),
        3, MAX ( 'Table'[Ship Date] )
    )
RETURN
    DATEDIFF ( _start, _end, MINUTE )

Output

You can refer to the following link to know more about field paramater

https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters#create-a-field-p...

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Wendy100]

thank you very much for your reply @Anonymous , but I am afraid there is gap to what I need. I down load the your shared file and change b to a, what I need is the avg instead of max.