Forum Discussion

TheGreatestGoat's avatar
TheGreatestGoat
Frequent Visitor
8 years ago
Solved

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: ...
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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's avatar
    Zubair_Muhammad
    8 years ago

    TheGreatestGoat

     

    Try this MEASURE to do the average

     

    Measure =
AVERAGEX (
    ALLSELECTED ( 'Sales Fact Table'[Customer] ),
    [Days between second order and third order]
)
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
8 years ago

Hi,

 

Share some data and show the expected result.

TheGreatestGoat's avatar
TheGreatestGoat
Frequent Visitor
8 years ago

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!

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 years ago

    Hi,

     

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

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 years ago

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

  • TheGreatestGoat's avatar
    TheGreatestGoat
    Frequent Visitor
    8 years ago

    Hi Ashish, thanks for your assistence.

     

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

     

    So confused

  • TheGreatestGoat's avatar
    TheGreatestGoat
    Frequent Visitor
    8 years ago

    Absolutely Ashish, appreciate your help!

  • Devesh790's avatar
    Devesh790
    Frequent Visitor
    3 years ago

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

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

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