Forum Discussion
Calculating Time to Return Across Multiple Rows
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!!
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
2 Replies
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
Hi,
Perhaps add your manually calculated expected results for that dataset. Thay way potential solutions can be tested.
Regards
