Get next visit on a customer

[ruban]

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

[Anonymous]

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 · Accepted solution]

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]

Hi Raj,

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

Regards,

Ruban 

[Anonymous]

Its same as above image. Thanks Raj

[ruban]

Hi Raj,

When I use your DAX for the calculated column , I get the previous visit date instead of next visit date . Could you hep me solve it?

[Anonymous]

oh, got it. I tested the code for past date as well and paasted that code here. Updated my above post, try it now.

Thanks

Raj

[ruban]

 thanks Raj. It worked perfectly.

Regards,

Ruban