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 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 )
)
PBI_Member_01's avatar
PBI_Member_01
Helper III
3 years ago

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's avatar
tamerj1
Community Champion
3 years ago

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's avatar
    PBI_Member_01
    Helper III
    3 years ago

    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's avatar
      tamerj1
      Community Champion
      3 years ago

      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's avatar
        PBI_Member_01
        Helper III
        3 years ago

        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