Calculating Time to Return Across Multiple Rows

[lheilig]

Hi All! I've been using Power BI for a little while now, but my Google Fu is failing me on this one. 

I have a list of customers with Unique Ids and their Dates of Arrive and Departure. I can easily capture my frequent flyers and how often they use our services but what I'm really looking for is time between visits. Here's an example of my data table:

ID	Last	First	Arrive	Depart
548	Ross	Betsy	5/17/22	5/20/22
548	Ross	Betsy	5/30/22	6/3/22
233	Lipa	Dua	5/17/22	5/19/22
233	Lipa	Dua	6/1/22	6/5/22
233	Lipa	Dua	6/20/22	6/23/22
667	Lopez	Jennifer	5/20/22	5/25/22
999	Wayne	Bruce	5/19/22	5/30/22
999	Wayne	Bruce	6/15/22	6/17/22
999	Wayne	Bruce	6/30/22	7/4/22

So what I'm trying to find is: for each person that visits multiple times, what was the interval between the departure and the next arrival.  I cannot seem to find the solution for this one. 

Any help is greatly appreciated!!

[Anonymous · Accepted solution]

Hi @lheilig ,

Please check the formula.

Column = 
var next_arrive = CALCULATE(MIN('Table'[Arrive]),FILTER(ALLEXCEPT('Table','Table'[ID]),'Table'[Depart]>EARLIER('Table'[Depart])))
return
IF(ISBLANK(next_arrive),0,DATEDIFF('Table'[Depart],next_arrive,DAY))

Best Regards,

Jay

[Jos_Woolley]

Hi,

Perhaps add your manually calculated expected results for that dataset. Thay way potential solutions can be tested.

Regards