Forum Discussion
Creating virtual table to calculate average difference between two dates in different tables
Hi Everyone!
Hopefully this is trivial for some of you. Pretty new to PowerBi, working with data from SAP tables.
I am trying to calculate the lead time from Purchase Order to Goods Receipt, which are both maintained in different tables.
I have imported the following tables:
EKPO = Purchase Order Item
EKBE = History per Purchase Order
EKKO = Purchase Order Header
EKKO has 1 - * with EKPO
EKPO has 1 - * EKBE with a calculated key of EBELN-EBELP (Purchase order number-Purchase order item)
Date of Purchase order can be found in EKPO and EKKO. Lets call it PO_Date
Date of Goods Receipt can be found in EKBE. Lets call it GR_Date
What I want to calculate is the average lead time for a given material number. For instance, if material number 1234 has been purchased four times, I want to calculate the average days between PO_Date and GR_Date on those four purchases (would be four rows in EKBE and four rows in EKPO).
Trying to understand virtual tables and addcolumns functionality in DAX Studio, but I am a bit stuck.
I would also like to calculate the standard deviation for such expression. Using this, I hope to visualize historic lead times for all the materials in my warehouse.
Would appreciate any and all help!
For future google searches: SAP, SAPMM, Supply Chain, Lead time, Procurement, SAP MM
You could create a measure for the average lead time like
Avg lead time = AVERAGEX ( EKPO, VAR purchaseOrderDate = SELECTEDVALUE ( EKPO[PO_Date] ) VAR deliveryDate = SELECTCOLUMNS ( FILTER ( RELATEDTABLE ( EKBE ), EKBE[Type] = "Delivery" ), "@val", EKBE[GR_Date] ) RETURN DATEDIFF ( purchaseOrderDate, deliveryDate, DAY ) )and for the standard deviation just change AVERAGEX to STDEVX.P Put them into a visual with the material.
2 Replies
You could create a measure for the average lead time like
Avg lead time = AVERAGEX ( EKPO, VAR purchaseOrderDate = SELECTEDVALUE ( EKPO[PO_Date] ) VAR deliveryDate = SELECTCOLUMNS ( FILTER ( RELATEDTABLE ( EKBE ), EKBE[Type] = "Delivery" ), "@val", EKBE[GR_Date] ) RETURN DATEDIFF ( purchaseOrderDate, deliveryDate, DAY ) )and for the standard deviation just change AVERAGEX to STDEVX.P Put them into a visual with the material.