Forum Discussion

PBI_Member_01's avatar
PBI_Member_01
Icon for Helper III rankHelper III
3 years ago
Solved

Calculate Average Days for First Order (For All Customers)

Hi everyone, I am having troubles creating this measure in Power BI. The dataset I am connected to is DirectQuery. Please see image below for reference. I want to create a measure which a...
  • tamerj1's avatar
    tamerj1
    3 years ago

    PBI_Member_01 

    Ok I hope this will work 

    Days To First Order =
AVERAGEX (
    VALUES ( CUSTOMER[Cust ID] ),
    VAR DateCreated =
        CALCULATE ( MAX ( CUSTOMER[Cust Date Created] ) )
    VAR FirstOrderDate =
        CALCULATE ( MIN ( ORDERS[Order Date] ), ORDERS[Order Type] IN { "A", "B" } )
    RETURN
        DATEDIFF ( DateCreated, FirstOrderDate, DAY )
)
Karthikstark's avatar
Karthikstark
Frequent Visitor
3 years ago

You can try this measure

 

AverageDaysTakenForFirstOrderForA&B =

DIVIDE (

    SUMX (

        FILTER ( tableOfYourValues, orderType = "A" || orderType = "B" ),

        timeToFirstOrder

    ),

    COUNTROWS ( FILTER ( tableOfYourValues, orderType = "A" || orderType = "B" ) )

)

 

  • PBI_Member_01's avatar
    PBI_Member_01
    Icon for Helper III rankHelper III
    3 years ago

    Thank you for your suggestion on this, I tried but this is not giving the correct values.

    What I want is, From the time when a customer was created (Information in Table A, using Cust Date Created) till the time (days) customer placed their first order (Information in Table B, using Order Date) for Types A and B (basically filter minimum order date for only order types A and B), have this calculated for every cust ID in Table A (something like DATEDIFF(Cust Date Created,Order Date,DAY) )

    And then for report, display on card visual, the Average of this value, which will basically give a single number representing on average, it takes " X " number of days for customer to place order.

    Hope this brings some clarity and if not, let me know if I can add anything further on this.

    Thanks and Regards