Calculate average duration of status per product & per order

[JKVM]

Hi All,

Am relatively new and cannot figure out how to do this in PowerBI. Any suggestions would be very welcome!!

In our application, one order may have multiple products, each of which have their own workflow. All status updates of all products in all orders are tracked in an action log: Excel file with sample data (In this file just 3 orders)

I would like to calculate over time (for a selected period and/or selected product and/or selected status:

1. the average duration an order stays in a particular status. Eg. Product 1 stays in status each of the statuses on average 25 hours (workdays only). 
2.  the overall time between the start (1) and closed (9) status per product and per order.
3. the deviation of each product and order against the average

Please suggest 😉

Best,
Jan

[v-shex-msft · Accepted solution]

Hi @JKVM,

#1, I think you need to add a measure to calculate the diff between the current and next status based on the product id. Then you can use these with summarize function to get the average of each status.

All the secrets of SUMMARIZE - SQLBI

current status duration=
VAR currstatus =
    SELECTEDVALUE ( Table[orderStatusId] )
VAR currDate =
    MAX ( Table[date] )
VAR nextDate =
    CALCULATE (
        MIN ( Table[date] ),
        FILTER (
            ALLSELECTED ( Table ),
            [date] > currDate
                && [orderStatusId] <> currstatus
        ),
        VALUES ( Table[orderId] ),
        VALUES ( Table[product] )
    )
RETURN
    DATEDIFF ( currDate, nextDate, SECOND )

#2, You can use the current date to find out the previous start date and the next end date, then use these two values to get the product total durations.

OA duration by product of current order =
VAR currDate =
    MAX ( Table[date] )
VAR prevDate =
    CALCULATE (
        MAX ( Table[date] ),
        FILTER ( ALLSELECTED ( Table ), [date] < currDate && [orderStatusId] = 1 ),
        VALUES ( Table[orderId] ),
        VALUES ( Table[product] )
    )
VAR nextDate =
    CALCULATE (
        MIN ( Table[date] ),
        FILTER ( ALLSELECTED ( Table ), [date] > currDate && [orderStatusId] = 9 ),
        VALUES ( Table[orderId] ),
        VALUES ( Table[product] )
    )
RETURN
    DATEDIFF ( currDate, nextDate, SECOND )

#3, you can add a new formula to aggregate the second formula result to get the average, then you can use these to get the difference of average results.

Regards,

Xiaoxin Sheng