Forum Discussion
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 dat...
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!
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!
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!
