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

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
    Icon for Helper III rankHelper 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
      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