cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ruban Frequent Visitor
Frequent Visitor

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.

next visit.JPG 

 

Thanks,

Ruban

1 ACCEPTED SOLUTION

Accepted Solutions
rajendran Super Contributor
Super Contributor

Re: Get next visit on a customer

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!

View solution in original post

7 REPLIES 7
rajendran Super Contributor
Super Contributor

Re: Get next visit on a customer

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:

 

Next Visit Date.PNG

Thanks

Raj

 

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

Proud to be a Datanaut!

rajendran Super Contributor
Super Contributor

Re: Get next visit on a customer

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!

View solution in original post

ruban Frequent Visitor
Frequent Visitor

Re: Get next visit on a customer

Hi Raj,

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

 

Regards,

Ruban 

rajendran Super Contributor
Super Contributor

Re: Get next visit on a customer

Its same as above image. Thanks Raj

ruban Frequent Visitor
Frequent Visitor

Re: Get next visit on a customer

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?

next visit 2.JPG

Highlighted
rajendran Super Contributor
Super Contributor

Re: Get next visit on a customer

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 Frequent Visitor
Frequent Visitor

Re: Get next visit on a customer

 thanks Raj. It worked perfectly.

 

Regards,

Ruban 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 290 members 2,862 guests
Please welcome our newest community members: