Forum Discussion

SAU1111's avatar
SAU1111
Frequent Visitor
4 years ago
Solved

Average Time between Repair Work orders

Hello All,   I have a table where cloumn1 = Porduct Name , Cloumn 2 = Serial Numbers of products , Cloumn 3 = Repair Work Order Numbers , Cloumn 4 = Work order Created date , Column 5 = Repair Work...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi SAU1111 ,

    If the Repair Work Order Numbers is random like below:

    You can create a column to rank the Closed Date for each Product Name and Serial Numbers of products, like this:

    Rank =
RANKX (
    FILTER (
        'Table',
        'Table'[Porduct Name] = EARLIER ( 'Table'[Porduct Name] )
            && 'Table'[Serial Numbers of products]
                = EARLIER ( 'Table'[Serial Numbers of products] )
    ),
    'Table'[Repair Work Order Closed Date],
    ,
    ASC,
    DENSE
)

    Then create a measure.

    Average Diff =
VAR _T =
    ADDCOLUMNS (
        'Table',
        "Diff",
            DATEDIFF (
                MAXX (
                    FILTER (
                        'Table',
                        'Table'[Rank]
                            = EARLIER ( 'Table'[Rank] ) - 1
                            && 'Table'[Porduct Name] = EARLIER ( 'Table'[Porduct Name] )
                            && 'Table'[Serial Numbers of products]
                                = EARLIER ( 'Table'[Serial Numbers of products] )
                    ),
                    'Table'[Repair Work Order Closed Date]
                ),
                'Table'[Repair Work Order Closed Date],
                DAY
            )
    )
RETURN
    AVERAGEX ( _T, [Diff] )

    Get the result for each Product Name and Serial Numbers of products.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

     

SAU1111's avatar
SAU1111
Frequent Visitor
4 years ago

Sorry I forgot to mention that for 1 Serial numbers there can be N numbers of Work orders and there closed On date . Now problem is that more than 1 serial numbers has same repair work order closed on date . SO WOrk order numbers also be random cannot be arranged as on same day two Work order of a sequence like 1 and 2 can be for different serial numbers . Now First how to arrange by Serial Numbers all the WO and its closed on Date so that i can arrange the dates in ascending order and calculate a difference . 

v-yanjiang-msft's avatar
v-yanjiang-msft
Community Support
4 years ago

Hi SAU1111 ,

If the Repair Work Order Numbers is random like below:

You can create a column to rank the Closed Date for each Product Name and Serial Numbers of products, like this:

Rank =
RANKX (
    FILTER (
        'Table',
        'Table'[Porduct Name] = EARLIER ( 'Table'[Porduct Name] )
            && 'Table'[Serial Numbers of products]
                = EARLIER ( 'Table'[Serial Numbers of products] )
    ),
    'Table'[Repair Work Order Closed Date],
    ,
    ASC,
    DENSE
)

Then create a measure.

Average Diff =
VAR _T =
    ADDCOLUMNS (
        'Table',
        "Diff",
            DATEDIFF (
                MAXX (
                    FILTER (
                        'Table',
                        'Table'[Rank]
                            = EARLIER ( 'Table'[Rank] ) - 1
                            && 'Table'[Porduct Name] = EARLIER ( 'Table'[Porduct Name] )
                            && 'Table'[Serial Numbers of products]
                                = EARLIER ( 'Table'[Serial Numbers of products] )
                    ),
                    'Table'[Repair Work Order Closed Date]
                ),
                'Table'[Repair Work Order Closed Date],
                DAY
            )
    )
RETURN
    AVERAGEX ( _T, [Diff] )

Get the result for each Product Name and Serial Numbers of products.

I attach my sample below for reference.

 

Best Regards,
Community Support Team _ kalyj

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