Forum Discussion

Milan_TBC's avatar
Milan_TBC
Frequent Visitor
3 years ago
Solved

Average Customer Return Time

Hi,

I want to calculate the average return time of my customers over time. 
I have a table with general data such as Cust_ID, and order_date per order. 
Right now i have general calculations based on first_order and last_order, however this doesn't cover all averages, since some customers have 10+ orders. 

 

Is there a way to calculate the average time between orders? Using the regular DATE commands (DATEDIFF, DATESBETWEEN, etc,), i run into problems since most of them only accept two dates, not more. 

 

Thanks in advance!

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi @Manal_Edd ,

    I created a sample pbix file(see attachment) for you, please check whether that is what you want.

    1. Create two measures as below

    Datediff = 
VAR _selcust =
    SELECTEDVALUE ( 'Table'[lead_customer_id] )
VAR _seldate =
    SELECTEDVALUE ( 'Table'[closed_at] )
VAR _predate =
    CALCULATE (
        MAX ( 'Table'[closed_at] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[lead_customer_id] = _selcust
                && 'Table'[closed_at] < _seldate
        )
    )
RETURN
    DATEDIFF ( _predate, _seldate, DAY )
    average_orderTime = 
VAR _selcustid =
    SELECTEDVALUE ( 'Table'[lead_customer_id] )
VAR _count =
    CALCULATE (
        COUNT ( 'Table'[lead_customer_id] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[lead_customer_id] = _selcustid )
    )
VAR _sum =
    SUMX (
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[lead_customer_id] = _selcustid ),
        [Datediff]
    )
RETURN
    IF (
        ISBLANK ( [Datediff] ),
        0,
        IF ( _count = 1, _sum, ROUND ( DIVIDE ( _sum, _count - 1 ), 0 ) )
    )

    2. Create a table visual 

     

    If the above one can't help you get the desired result, please provide more sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Milan_TBC,  A new column

     

    Diff =

    var _max  = maxx(filter(Table, [Customer] = earlier([Customer]) && [Order date] < earlier({order date]) ) , [Order Date])

    return

    datediff(_max, [Order date], day)

     

    Then you can take AVg of this column

     

     

    Or move this in a Meausure

     

    AverageX(Table,

    var _max  = maxx(filter(Table, [Customer] = earlier([Customer]) && [Order date] < earlier({order date]) ) , [Order Date])

    return

    datediff(_max, [Order date], day) )

     

    Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Milan_TBC ,

    Could you please provide some sample data in your order table (exclude sensitive data) with Text format and your expected result with backend logic and special examples. For example: the data in the below table, which value you want to get? And How do you calculate it?  It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

  • Milan_TBC's avatar
    Milan_TBC
    Frequent Visitor
    3 years ago

     

    The following table is an indication for what is needed, where the yellow values are what i'm looking for!

     

    amitchandak Right now your solution does not seem to work, i recieve syntax errors, mainly "< earlier({order date]) )" does not seem to function correctly.

     

    Looking forward to any other solutions, Thanks in advance!

    • Milan_TBC's avatar
      Milan_TBC
      Frequent Visitor
      3 years ago

      Addendum;

       

      average_orderTime also has to be yellow, this value is not calculated right now!

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi @Manal_Edd ,

        I created a sample pbix file(see attachment) for you, please check whether that is what you want.

        1. Create two measures as below

        Datediff = 
VAR _selcust =
    SELECTEDVALUE ( 'Table'[lead_customer_id] )
VAR _seldate =
    SELECTEDVALUE ( 'Table'[closed_at] )
VAR _predate =
    CALCULATE (
        MAX ( 'Table'[closed_at] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[lead_customer_id] = _selcust
                && 'Table'[closed_at] < _seldate
        )
    )
RETURN
    DATEDIFF ( _predate, _seldate, DAY )
        average_orderTime = 
VAR _selcustid =
    SELECTEDVALUE ( 'Table'[lead_customer_id] )
VAR _count =
    CALCULATE (
        COUNT ( 'Table'[lead_customer_id] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[lead_customer_id] = _selcustid )
    )
VAR _sum =
    SUMX (
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[lead_customer_id] = _selcustid ),
        [Datediff]
    )
RETURN
    IF (
        ISBLANK ( [Datediff] ),
        0,
        IF ( _count = 1, _sum, ROUND ( DIVIDE ( _sum, _count - 1 ), 0 ) )
    )

        2. Create a table visual 

         

        If the above one can't help you get the desired result, please provide more sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

        How to upload PBI in Community

        Best Regards