Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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,...
Need Help
  • johnt75's avatar
    johnt75
    4 years ago

    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.

johnt75's avatar
johnt75
Super User
4 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thanks!!