Calculate A Customer's Second Order

[TheGreatestGoat]

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

[Zubair_Muhammad · Accepted solution]

HI @TheGreatestGoat

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
    )

[Zubair_Muhammad · Accepted solution]

HI @TheGreatestGoat

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
    )

[TheGreatestGoat]

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?

[Zubair_Muhammad · Accepted solution]

@TheGreatestGoat

Try this MEASURE to do the average

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

[TheGreatestGoat]

Hi @Zubair_Muhammad

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 =
    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
    )

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 

[Zubair_Muhammad]

@TheGreatestGoat

You could try this one

Date of second order =
VAR temp =
    ADDCOLUMNS (
        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] )

[TheGreatestGoat]

Hey @Zubair_Muhammad

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

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[TheGreatestGoat]

Hi @Ashish_Mathur, thanks for your reply!

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!

[Ashish_Mathur]

Hi,

Here's the result i got.  Download the PBI file from here.

[TheGreatestGoat]

Hi Ashish, thanks for your assistence.

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

So confused

[Ashish_Mathur]

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

[Devesh790]

Hi Ashish,
What if we wanted to see it in calculated coloumns

[Ashish_Mathur]

That is a very sold post.  Share some data, describe the question and show the expected result.

[TheGreatestGoat]

Absolutely Ashish, appreciate your help!

[Zubair_Muhammad]

@TheGreatestGoat

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

Please see attached file