Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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:
Please suggest 😉
Best,
Jan
Solved! Go to 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
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
User | Count |
---|---|
22 | |
14 | |
11 | |
9 | |
6 |
User | Count |
---|---|
24 | |
23 | |
20 | |
15 | |
10 |