Average Time Card

DAX DAX Calculations
1

Hello

Harsh was nice enough to solve my first problem, and I could not figure out my next problem. How can I calculate the average pick time per order displayed as a card?
Time To Pick Test.pbix (8.5 MB)

1 Like
2

@JDopson do you want the average of Duration Between Order and Ship Date - 1 ?
I am assuming that Duration Between Order and Ship Date - 1 is calculating the order level pick time you desire to average here

3

Hi @JDopson

Please create these two measures and pull the [Average time duration per order] in your power bi card .
Please let me know with your feedback; if this works for you and you see relevant results you desire

Please Create these two measure (Both new)

1)Duration Between Order and Ship Date_in_minutes

2)Average time duration per order

Duration Between Order and Ship Date_in_minutes =
VAR _Days_Calculation =
CALCULATE( COUNTROWS( ‘Date Table’ ) ,
DATESBETWEEN(
‘Date Table’[Date] ,
SELECTEDVALUE( Data[Order Date] ) ,
SELECTEDVALUE( Data[Ship Date] ) ) ,
‘Date Table’[DayOfWeekName] <> “Saturday” ,
‘Date Table’[DayOfWeekName] <> “Sunday” ,
‘Date Table’[Holiday] = BLANK() ,
ALLSELECTED( Data ) ) - 1

VAR _Minutes_Calculation =
ABS(
DATEDIFF(
SELECTEDVALUE( Data[Order Time] ) ,
SELECTEDVALUE( Data[Ship Time] ) ,
MINUTE ) )

RETURN
ABS(_Minutes_Calculation+_Days_Calculation*1440)

Duration Between Order and Ship Date - 1 =
VAR _Days_Calculation =
CALCULATE( COUNTROWS( ‘Date Table’ ) ,
DATESBETWEEN(
‘Date Table’[Date] ,
SELECTEDVALUE( Data[Order Date] ) ,
SELECTEDVALUE( Data[Ship Date] ) ) ,
‘Date Table’[DayOfWeekName] <> “Saturday” ,
‘Date Table’[DayOfWeekName] <> “Sunday” ,
‘Date Table’[Holiday] = BLANK() ,
ALLSELECTED( Data ) ) - 1

VAR _Time_Calculation =
FORMAT(
SELECTEDVALUE( Data[Order Time] ) - SELECTEDVALUE( Data[Ship Time] ) ,
“HH:MM:SS” )

VAR _Minutes_Calculation =
ABS(
DATEDIFF(
SELECTEDVALUE( Data[Order Time] ) ,
SELECTEDVALUE( Data[Ship Time] ) ,
MINUTE ) )

VAR _Results =
SWITCH( TRUE() ,
_Days_Calculation > 1 && _Minutes_Calculation > 60 ,
_Days_Calculation & " Days " & _Time_Calculation & " Hours" ,

_Days_Calculation > 1 && _Minutes_Calculation <= 60 , 
    _Days_Calculation & " Days " & _Time_Calculation & " Minutes" ,

_Days_Calculation <= 1 && _Minutes_Calculation > 60 , 
    _Days_Calculation & " Day " & _Time_Calculation & " Hours" ,

_Days_Calculation <= 1 && _Minutes_Calculation <= 60 , 
    _Days_Calculation & " Day " & _Time_Calculation & " Minutes" ,

BLANK() )

RETURN
_Results

image
image3750×1521 489 KB

Time To Pick Average Per day.pbix (8.5 MB)

4

Thank you so much for that great answer @Dhrubojit_Goswami

We hope this helped you @ @JDopson :slight_smile:

If not, how far did you get and what kind of help you need further?

If yes, kindly mark as solution the answer that solved your query.

5

Hi @JDopson, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, please create a new thread.