cancel
Showing results for
Did you mean:
Frequent Visitor

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.

Thanks

2 ACCEPTED SOLUTIONS
Community Champion

You can try this MEASURE pattern

Days between second order and third order =
VAR temp =
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
)
Regards
Zubair

Community Champion

@TheGreatestGoat

Try this MEASURE to do the average

Measure =
AVERAGEX (
ALLSELECTED ( 'Sales Fact Table'[Customer] ),
[Days between second order and third order]
)
Regards
Zubair

13 REPLIES 13
Community Champion

You can try this MEASURE pattern

Days between second order and third order =
VAR temp =
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
)
Regards
Zubair

Frequent Visitor

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 Days

Customer 2: 8 Days

Customer 3: 12 Days

Or will I have to create a calculated column?

Community Champion

@TheGreatestGoat

Try this MEASURE to do the average

Measure =
AVERAGEX (
ALLSELECTED ( 'Sales Fact Table'[Customer] ),
[Days between second order and third order]
)
Regards
Zubair

Frequent Visitor

This is working great and I have marked these as solutions.

If I wanted to modify this formula to extract the customer's second purchase date i.e. Joe's second order occurred on 01/07/2018 in a new column, how would I adjust the following formula you provided:

Days between second order and third order =
VAR temp =
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
)

So basically, if the rank of a customer's order = 2 (second order) then return this second order date in a new column.

I have tried many formulas but cannot get it to work

Community Champion

@TheGreatestGoat

You could try this one

Date of second order =
VAR temp =
VALUES ( 'Sales Fact Table'[purchase_Date] ),
"RANK", RANKX (
VALUES ( 'Sales Fact Table'[purchase_Date] ),
[purchase_Date],
,
ASC,
DENSE
)
)
RETURN
MINX ( FILTER ( temp, [RANK] = 2 ), [purchase_Date] )
Regards
Zubair

Frequent Visitor

Thanks for this suggestion. Unfortunately this does not seem to be working. It returns the same value for each customer. 24/07/2013.

It would seem perhaps that it is returning the first second order date that existed across all customers and assigning it to each customer rather than returning a unique value for each customer

Super User III

Hi,

Share some data and show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Data Is as Follows:

 purchase_date product_id order_id customer_id 24/05/2014 100 6970030474 6911468682 24/05/2014 200 6970029258 6911468682 24/05/2014 1111 6970028426 6911468682 24/05/2014 800 6970030090 6911468682 24/05/2014 100 6970029578 6911468682 24/05/2014 2000 6970028810 6911468682 27/05/2014 1100 6970098378 6911468682 1/06/2014 400 6970186122 6911468682 1/06/2014 900 6970186506 6911468682 1/06/2014 100 6970186954 6911468682 5/09/2013 3000 6966721034 6904791178 5/09/2013 2000 6966721034 6904791178 5/09/2013 1000 6966721034 6904791178 7/07/2014 900 6970956234 6904791178 8/07/2014 800 6970970506 6904791178 10/07/2014 1100 6970999754 6904791178 11/07/2014 400 6971025482 6904791178 29/01/2015 300 6980551690 6904791178 29/01/2015 100 6980551114 6904791178 29/01/2015 200 6980551690 6904791178 29/01/2015 200 6980551114 6904791178 29/01/2015 200 6980551114 6904791178 29/01/2015 50061 6980551114 6904791178 29/01/2015 1300 6980551690 6904791178 11/02/2015 20000 6980978890 6904791178 11/02/2015 6174 6980978890 6904791178 11/02/2015 8946 6980978890 6904791178

I have a measure which returns First Order Date:- Date Of First Purchase = FIRSTDATE('Sales Fact Table'[purchase_date])

I have a measure which returns Last Order Date:- Date Of Last Purchase = LASTDATE('Sales Fact Table'[purchase_date])

I would now like in a seperate column to return the customer's second purchase date

So for customer 6904791178 it would return the value 7/07/2014 as this was their date of second purchase

Thanks!

Super User III

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Hi Ashish, thanks for your assistence.

I tried the solution in your PBIX file but it says I have a circular dependancy:

So confused

Super User III

I really cannot helop now.  The maximum i can do is share the file with you which i already have done.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Community Champion

@TheGreatestGoat

WHen I use my formula as a MEASURE with sample data...I get correct results

Regards
Zubair

Announcements

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors