cancel
Showing results for
Search instead for
Did you mean:
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.

Thanks,

Ruban

1 ACCEPTED SOLUTION

Accepted Solutions
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
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:

Thanks

Raj

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

Proud to be a Datanaut!

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!

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
Super Contributor

## Re: Get next visit on a customer

Its same as above image. Thanks Raj

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?

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

Frequent Visitor

## Re: Get next visit on a customer

thanks Raj. It worked perfectly.

Regards,

Ruban

## Helpful resources

Announcements

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

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

#### Watch Sessions On Demand!

Continue your learning in our online communities.

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