Is this possible in DAX? Do I need to Modify my table?

[b2wise]

Hi all,

I have a table that shows when an item arrives in the warehouse and when it's put away, the arrivals (IND), and put-aways (PUT), are shown on seperate lines. Each item can have multiple lines for arrival and putaway.

I need to find the average amount of time between an item's first arrival and first putaway, this number would be shown in a card visual.

Is this possible with this type of table or do I need to do more data modeling?

Sample tabe below. To track a unique item use order_no concatenated with line_no, Record_Type shows if its IND (arrival) or PUT (putaway). A bonus would be to see avg. time if backorder_active = "Y". Thank you!!!

RECORD_TYPE	ORDER_NO	LINE_NO	DATE_CREATED	TIME_CREATED	vWT_PORECEIPT_PIDS.BACKORDER_ACTIVE
IND	54237249	1	Monday, January 11, 2021	12:17:56 PM	N
IND	54237249	1	Monday, January 11, 2021	12:17:56 PM	N
IND	54237249	1	Monday, January 11, 2021	12:17:56 PM	N
IND	54236929	1	Monday, January 11, 2021	11:48:12 AM	N
IND	54236929	1	Monday, January 11, 2021	11:48:12 AM	N
IND	54237252	2	Monday, January 11, 2021	12:20:23 PM	Y
IND	54237249	2	Monday, January 11, 2021	12:12:18 PM	N
IND	54237252	1	Monday, January 11, 2021	12:17:18 PM	N
IND	54235982	1	Monday, January 11, 2021	11:37:09 AM	Y
PUT	54237252	2	Tuesday, January 12, 2021	8:02:58 AM	Y
PUT	54237249	2	Tuesday, January 12, 2021	8:02:26 AM	N
PUT	54237249	1	Tuesday, January 12, 2021	8:06:11 AM	N
PUT	54237249	1	Tuesday, January 12, 2021	8:08:17 AM	N
PUT	54237249	1	Tuesday, January 12, 2021	8:08:17 AM	N
PUT	54236929	1	Tuesday, January 12, 2021	7:57:01 AM	N
PUT	54236929	1	Tuesday, January 12, 2021	7:57:02 AM	N
PUT	54237252	1	Tuesday, January 12, 2021	8:08:16 AM	N
PUT	54235982	1	Wednesday, January 13, 2021	2:11:37 AM	Y

[Jihwan_Kim]

AVG hours =

var _newtable=

ADDCOLUMNS (

SUMMARIZE ( Data, Order_No[ORDER_NO], Line_No[LINE_NO] ),

"@first_arrival",

CALCULATE (

VAR currentrecord = "IND"

VAR currentorder =

MAX ( Order_No[ORDER_NO] )

VAR currentline =

MAX ( Line_No[LINE_NO] )

RETURN

CALCULATE (

MIN ( Data[Date & Time] ),

Record_Type[RECORD_TYPE] = currentrecord,

Order_No[ORDER_NO] = currentorder,

Line_No[LINE_NO] = currentline

)

),

"@first_putaway",

CALCULATE (

VAR currentrecord = "PUT"

VAR currentorder =

MAX ( Order_No[ORDER_NO] )

VAR currentline =

MAX ( Line_No[LINE_NO] )

RETURN

CALCULATE (

MIN ( Data[Date & Time] ),

Record_Type[RECORD_TYPE] = currentrecord,

Order_No[ORDER_NO] = currentorder,

Line_No[LINE_NO] = currentline

)

)

)

return

AVERAGEX(_newtable, DATEDIFF([@first_arrival], [@first_putaway],HOUR))

https://www.dropbox.com/s/gklpxflw79h4f0z/bewise.pbix?dl=0 

[b2wise]

Thank you @Jihwan_Kim! Can you explain why you made the three additional tables? I was able to recreate your measure using only the Data table.

Also would putting a visual level filter for "Backorder Active" "Y "or "N" return the correct results?

[v-xiaotang]

Hi @b2wise 

the reason I think why Jihwan_Kim made the 3 additional tables is he didn't have your sample, then he create them as dimension tables.  Although not necessary, dimension table does contribute to model management. 

Best Regards,

Community Support Team _Tang

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