Creating virtual table to calculate average difference between two dates in different tables

[newGuyAnalyzing]

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

[johnt75 · Accepted solution]

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.

[newGuyAnalyzing]

Thanks!!