Forum Discussion

tmaki828's avatar
tmaki828
Regular Visitor
7 years ago
Solved

Duration

I'm looking for a way to calculate the number of days between when a Service Order status is "2-RECEIVED" to "5-QUOTED".  We are looking to report the average it takes orders to go from Received to Quote.  Please note that there could be multiple Service Orders records for one Service Order.

 

Example:  How long did it take SRVO-006460 to go from Received to Quote.  Our SLA = 7 days.

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    HI tmaki828,

     

    You can use following measure to calculate datediff based on current 'document no' and 'before' 'after' fields tag:

    Measure =
VAR currNo =
    SELECTEDVALUE ( Table[Document No_] )
VAR list =
    CALCULATETABLE (
        VALUES ( Table[Change Date] ),
        FILTER (
            ALLSELECTED ( Table ),
            [Document No_] = currNo
                && [Before] = "2-RECEIVED"
                && [After] = "5-QUOTED"
        )
    )
RETURN
    DATEDIFF ( MINX ( list, [Change Date] ), MAXX ( list, [Change Date] ), DAY )

     

    Regards,

    Xiaoxin Sheng

2 Replies

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Hi tmaki828

    Is it not 10 days in the example shown?

    Please paste some text format data tha can be readily copied on top of the screen capture. 

    Is what you show the full table you're working with (I mean all fields)?

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    HI tmaki828,

     

    You can use following measure to calculate datediff based on current 'document no' and 'before' 'after' fields tag:

    Measure =
VAR currNo =
    SELECTEDVALUE ( Table[Document No_] )
VAR list =
    CALCULATETABLE (
        VALUES ( Table[Change Date] ),
        FILTER (
            ALLSELECTED ( Table ),
            [Document No_] = currNo
                && [Before] = "2-RECEIVED"
                && [After] = "5-QUOTED"
        )
    )
RETURN
    DATEDIFF ( MINX ( list, [Change Date] ), MAXX ( list, [Change Date] ), DAY )

     

    Regards,

    Xiaoxin Sheng