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

     

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.