Forum Discussion

Wendy100's avatar
Wendy100
Frequent Visitor
2 years ago
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 drill down.

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

Need Help
  • Wendy100's avatar
    Wendy100
    2 years ago

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

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

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

     

    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's avatar
      Wendy100
      Frequent Visitor
      2 years ago

      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.

       

  • Wendy100's avatar
    Wendy100
    Frequent Visitor
    2 years ago

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