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]
)
TheGreatestGoat's avatar
TheGreatestGoat
Frequent Visitor
8 years ago

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's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
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]
)
  • TheGreatestGoat's avatar
    TheGreatestGoat
    Frequent Visitor
    8 years ago

    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's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      8 years ago

      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's avatar
        TheGreatestGoat
        Frequent Visitor
        8 years ago

        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