Forum Discussion
Calculate A Customer's Second Order
Hi There,
I am trying to calculate the time between a customer's first order and second order. I have been able to calculate the difference between first and last date as follows using measures:
Date Of First Purchase = FIRSTDATE('Sales Fact Table'[purchase_date])
Date Of Last Purchase = LASTDATE('Sales Fact Table'[purchase_date])
Days Between First And Last = DATEDIFF([Date Of First Purchase], [Date Of Last Purchase], day )
However I want to find the difference between first order and second order, then between second order and third order and so on.
Anyone know how I would go about this?
Thanks
You can try this MEASURE pattern
Days between second order and third order = VAR temp = ADDCOLUMNS ( VALUES ( 'Sales Fact Table'[purchase_Date] ), "RANK", RANKX ( VALUES ( 'Sales Fact Table'[purchase_Date] ), [purchase_Date], , DESC, DENSE ) ) RETURN DATEDIFF ( MINX ( FILTER ( temp, [RANK] = 3 ), [purchase_Date] ), MINX ( FILTER ( temp, [RANK] = 2 ), [purchase_Date] ), DAY )Try this MEASURE to do the average
Measure = AVERAGEX ( ALLSELECTED ( 'Sales Fact Table'[Customer] ), [Days between second order and third order] )
15 Replies
- Zubair_Muhammad
Community Champion
You can try this MEASURE pattern
Days between second order and third order = VAR temp = ADDCOLUMNS ( VALUES ( 'Sales Fact Table'[purchase_Date] ), "RANK", RANKX ( VALUES ( 'Sales Fact Table'[purchase_Date] ), [purchase_Date], , DESC, DENSE ) ) RETURN DATEDIFF ( MINX ( FILTER ( temp, [RANK] = 3 ), [purchase_Date] ), MINX ( FILTER ( temp, [RANK] = 2 ), [purchase_Date] ), DAY )Hi Zubair,
This works great. Thanks! Do you know how I then average all of the rows in the measure column?
E.g.
Customer 1: 3 DaysCustomer 2: 8 Days
Customer 3: 12 Days
Or will I have to create a calculated column?
- Zubair_Muhammad
Try this MEASURE to do the average
Measure = AVERAGEX ( ALLSELECTED ( 'Sales Fact Table'[Customer] ), [Days between second order and third order] )
