topic Re: Average Customer Return Time in DAX Commands and Tips

Average Customer Return Time

Milan_TBC — Thu, 03 Nov 2022 15:17:39 GMT

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!

Re: Average Customer Return Time

amitchandak — Fri, 04 Nov 2022 02:10:04 GMT

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

Re: Average Customer Return Time

Anonymous — Fri, 04 Nov 2022 05:39:42 GMT

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

Re: Average Customer Return Time

Milan_TBC — Fri, 04 Nov 2022 11:09:47 GMT

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!

Re: Average Customer Return Time

Milan_TBC — Fri, 04 Nov 2022 11:10:47 GMT

Addendum;

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

Re: Average Customer Return Time

Anonymous — Mon, 07 Nov 2022 09:14:50 GMT

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

Re: Average Customer Return Time

Milan_TBC — Mon, 07 Nov 2022 12:17:09 GMT

Thanks a lot!