Forum Discussion

PBI_Member_01's avatar
PBI_Member_01
Helper 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 and display it on card visual, showing average days taken to place first order (taking into account order dates for only Order types A and B). Basically this will be Datediff(Minimum Order Date - Cust Date Created).
And then display on number card Average number of days taken for all customers to place first order (again, only for types A and B)

I have been stuck on this for quite some time and have tried various approaches but the results are no way closer to what I want to achieve.

Any help on this would be appreciated. In bit of an urgency. Please let me know if I can add anything on this.

Thanks in Advance

Kind Regards

  • 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 )
)

12 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi PBI_Member_01 

    please try

     

    Days To First Order =
VAR DateCreated =
    MAX ( CUSTOMER[Cust Date Created] )
RETURN
    AVERAGEX (
        VALUES ( CUSTOMER[Cust ID] ),
        VAR FirstOrderDate =
            CALCULATE (
                MIN ( ORDERS[Order Date] ),
                REMOVEFILTERS (),
                VALUES ( CUSTOMER[Cust ID] ),
                ORDERS[Order Type] IN { "A", "B" }
            )
        RETURN
            DATEDIFF ( DateCreated, FirstOrderDate, DAY )
    )

     

    • PBI_Member_01's avatar
      PBI_Member_01
      Helper III
      3 years ago

      Hi tamerj1 ,

      Thank you for your quick response on this. I am currently implementing this and will update you shortly on this.

      Can you please confirm if I can actually test this out, for instance in a table matrix against each customer ID,if the days being calculated are correct for every customer and then the actual average for all customers?

      Many thanks in advance

      Regards

      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        PBI_Member_01 

        I didn't test to be honest but it is is designed to work in both table visual sliced by Cust ID as well as in a card visual. 
        please test and let me know if anything goes wrong. 

  • 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
      Helper 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