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 )
)
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity 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. 

PBI_Member_01's avatar
PBI_Member_01
Icon for Helper III rankHelper 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
    Icon for Community Champion rankCommunity 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
      Icon for Helper III rankHelper 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
        Icon for Community Champion rankCommunity 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 )
    )