Calculate Average Days for First Order (For All Customers)

[PBI_Member_01]

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]

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]

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]

@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. 

[PBI_Member_01]

Hi @tamerj1 ,

Thank you for your help on this one. I am surprised you didn't manage to test this one and yet you have come up with this elegant solution. This is very close to what I want to achieve.

So here is my feedback on this after some testing, it does calculate the day to first order correctly for each row, but I think it is not calculating the Average correctly. Please take a look at the screenshot below:

So the values that you can see 21, 12, 24, 2, and 1 are correctly calculated for the respective cust IDs. However, the average does not seem to be correct and looks off. Shouldn't the average be 12 in this case?

This has been of great help so far. Would really appreciate if you can look into this.

Thanks & Kind Regards

[tamerj1]

@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] ),
                ALLEXCEPT ( CUSTOMER, CUSTOMER[Cust ID] ),
                ALL ( ORDERS ),
                ORDERS[Order Type] IN { "A", "B" }
            )
        RETURN
            DATEDIFF ( DateCreated, FirstOrderDate, DAY )
    )

[PBI_Member_01]

Hi @tamerj1 ,

I tried the above solution but now it is going completely off even on individual customer level. Please have a look at image below:

So as opposed to the correct values of 21, 12, 24, 2, and 1 which I was getting before by using your calculation, now even those values are off.

Can you please take a look at this again?

Highly appreciate you putting your time and effort on this.

Thanks and Regards

[tamerj1]

@PBI_Member_01 

ok, let's go 2 steps backwards and try the following. Let's see how it behaves. 

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

[PBI_Member_01]

Hi @tamerj1 ,

Okay so now the individual numbers are back to correct values, but the average still seems to be an issue. Please see the image below:

Thank you once again for your input on this.

Kind Regards

[tamerj1 · Accepted solution]

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

[PBI_Member_01]

Hi @tamerj1 ,

Just tried this and yes this works perfectly well !

Thank you for your amazing support on this !

Regards

[Karthikstark]

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]

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