Calculating the average time difference between orders,

SophieRawcliffe

Hi community, 

My data has the customer ID and then the date they made a purchase. I want to work out the average time between purchases per customer but not sure where to start. Some customers have 1 purchase, some have a lot. 

Thanks for any help 

CustomerID	Date of transaction
10118500	11/06/2024
10118500	22/05/2023
10118500	02/05/2024
10118500	01/01/2023
10118500	12/12/2023
10189549	11/09/2023
10189549	07/07/2024
10915523	11/12/2023
10049535	04/05/2022
10049535	05/05/2022
10049535	06/05/2022
10049535	23/04/2024

griffinwelsh

@SophieRawcliffe take a look at the attached solution. You need to do the following:

1. Convert your date string to date time

2. Sort on customer id and date

3. Use the multi-row formula to calculate difference between dates. (make sure to group by customer)

4. Filter out nulls

5. Use the summarize tool to obtain the average time between orders

6. Find customers with only one order and union them back into the final result (optional)

Solution Time Between Orders.yxmd