Forum Discussion

ruban's avatar
ruban
Frequent Visitor
7 years ago
Solved

Get next visit on a customer

Hi,

I have a table which contains customer id and visit date . I want to create a new column which holds the next visit  date. Example. If a customer has 3 visits. I wish to see the second visit date on the new column of the same row of first visit and so on.

I feel it could be done by creating an index column but I am not completely sure on how to make do it. Attaching an image to explain my requirement.

 

 

Thanks,

Ruban

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi ruban 

     

    The same solution can be achedived by creating a calculated column as well.

     

    Next Visist Date = CALCULATE(MIN('Customer Visit Table'[Visit_Date]),
                                FILTER('Next Visit Date','Customer Visit Table'[CID]= EARLIER('Customer Visit Table'[CID]) 
                                 && 'Customer Visit Table'[Visit_Date] >EARLIER('Customer Visit Table'[Visit_Date])))

    Please note that this  will not respond to slicer selection as its calculated column.

     

    Thanks

    Raj

     

    Did i answer your Question? Please mark my answer as Solution.

    Proud to be a Datanaut!

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi ruban 

     

    Please create the new calculated table using this DAX:

    New Next Visit Table = 
VAR vSelfjoin= FILTER( 
                     GENERATE('Customer Visit Table',
                               SELECTCOLUMNS('Customer Visit Table',
                                 "xCID" , 'Customer Visit Table'[CID],
                                 "xVisit_Date", 'Customer Visit Table'[Visit_Date])
                        ) 
                        ,'Customer Visit Table'[CID]=[xCID] &&
                         [xVisit_Date]>'Customer Visit Table'[Visit_Date]
)    
VAR vLastVisitDate = GROUPBY(vSelfjoin,
                              'Customer Visit Table'[CID],
                              'Customer Visit Table'[Visit_Date],
                              "Next Visit Date", MINX(CURRENTGROUP(),[xVisit_Date])
                              )  
RETURN NATURALLEFTOUTERJOIN('Customer Visit Table',vLastVisitDate)

    Then pull the columns from this new table:

     

    Thanks

    Raj

     

    Did i answer your Question? Please mark my answer as Solution.

    Proud to be a Datanaut!

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi ruban 

       

      The same solution can be achedived by creating a calculated column as well.

       

      Next Visist Date = CALCULATE(MIN('Customer Visit Table'[Visit_Date]),
                                FILTER('Next Visit Date','Customer Visit Table'[CID]= EARLIER('Customer Visit Table'[CID]) 
                                 && 'Customer Visit Table'[Visit_Date] >EARLIER('Customer Visit Table'[Visit_Date])))

      Please note that this  will not respond to slicer selection as its calculated column.

       

      Thanks

      Raj

       

      Did i answer your Question? Please mark my answer as Solution.

      Proud to be a Datanaut!

      • ruban's avatar
        ruban
        Frequent Visitor
        7 years ago

        Hi Raj,

        Could you share the table image when you use calculated column?

         

        Regards,

        Ruban