Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
JKVM
New Member

Calculate average duration of status per product & per order

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

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.