Average Customer Return Time

[Milan_TBC]

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!

[Milan_TBC]

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]

Addendum;

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

[Anonymous · Accepted solution]

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

[Milan_TBC]

Thanks a lot!

[Anonymous]

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

[amitchandak]

@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