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!

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!

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 

Highlighted
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

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
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.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 310 members 3,023 guests
Please welcome our newest community members: