cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TheGreatestGoat
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. 

Anyone know how I would go about this?

Thanks

2 ACCEPTED SOLUTIONS
Zubair_Muhammad
Community Champion
Community Champion

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
    )
Regards
Zubair

Please try my custom visuals


View solution in original post

@TheGreatestGoat

 

Try this MEASURE to do the average

 

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

Please try my custom visuals


View solution in original post

13 REPLIES 13
Zubair_Muhammad
Community Champion
Community Champion

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
    )
Regards
Zubair

Please try my custom visuals


View solution in original post

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?


@TheGreatestGoat

 

Try this MEASURE to do the average

 

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

Please try my custom visuals


View solution in original post

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 

@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] )
Regards
Zubair

Please try my custom visuals


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

Hi,

 

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur, thanks for your reply!

Data Is as Follows: 

purchase_dateproduct_idorder_idcustomer_id
24/05/201410069700304746911468682
24/05/201420069700292586911468682
24/05/2014111169700284266911468682
24/05/201480069700300906911468682
24/05/201410069700295786911468682
24/05/2014200069700288106911468682
27/05/2014110069700983786911468682
1/06/201440069701861226911468682
1/06/201490069701865066911468682
1/06/201410069701869546911468682
5/09/2013300069667210346904791178
5/09/2013200069667210346904791178
5/09/2013100069667210346904791178
7/07/201490069709562346904791178
8/07/201480069709705066904791178
10/07/2014110069709997546904791178
11/07/201440069710254826904791178
29/01/201530069805516906904791178
29/01/201510069805511146904791178
29/01/201520069805516906904791178
29/01/201520069805511146904791178
29/01/201520069805511146904791178
29/01/20155006169805511146904791178
29/01/2015130069805516906904791178
11/02/20152000069809788906904791178
11/02/2015617469809788906904791178
11/02/2015894669809788906904791178

 


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!

Hi,

 

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

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish, thanks for your assistence.

 

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

circular.jpg

 

So confused

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
https://www.linkedin.com/in/excelenthusiasts/

Absolutely Ashish, appreciate your help!

@TheGreatestGoat

 

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

 

Please see attached file

 

SECONDORDER.png

Regards
Zubair

Please try my custom visuals


Helpful resources

Announcements
MBAS on Demand

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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

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