Figure out Origin and Destination based on Location column and Date

Question

I'm having a hard time wrapping my head around this. I am trying to build a route map and I need an origin and a destination to achieve this. My data looks something like this:

+-----------+-----+-----------+
|   Date    | ID  | Location  |
+-----------+-----+-----------+
| 2/7/2018  | 101 | LA        |
| 2/16/2018 | 101 | Seattle   |
| 2/17/2018 | 101 | San Diego |
| 2/26/2018 | 102 | Arlington |
| 3/20/2018 | 101 | Aberdeen  |
| 5/16/2018 | 102 | Mesquite  |
| 5/17/2018 | 102 | Reisor    |
| 6/12/2018 | 103 | Oxnard    |
+-----------+-----+-----------+

What I am looking to end up with:

+-----------+-----+-----------+-------------+
|   Date    | ID  |  Origin   | Destination |
+-----------+-----+-----------+-------------+
| 2/7/2018  | 101 | LA        | Seattle     |
| 2/16/2018 | 101 | Seattle   | San Diego   |
| 2/17/2018 | 101 | San Diego | Aberdeen    |
| 2/26/2018 | 102 | Arlington | Mesquite    |
| 3/20/2018 | 101 | Aberdeen  | Aberdeen    |
| 5/16/2018 | 102 | Mesquite  | Reisor      |
| 5/17/2018 | 102 | Reisor    | Reisor      |
| 6/12/2018 | 103 | Oxnard    | Oxnard      |
+-----------+-----+-----------+-------------+

I've tried every possible way I could think of in Power Query. I've sorted the table by Date, ID and Location then I've created a duplicate of the table and added two different indexes (one from 0 and the other starting from 1) and then I've merged them. It didn't really work out when I applied it to the entire data set. I've tried pivoting and unpivoting the columns. I'm out of ideas.

Can someone please suggest the best way to go about this in order to achieve the desired result, either in M or in DAX?

Thanks.

Answer 1

To sort this out, let's write down the rules:

1. A route is denoted by ID.
2. Origin is the same as Location.
3. For Destination, if it's the last date on the same route, then Destination = Origin; otherwise
4. Destination will be the Location where it is the earliest/minimum date that is later than the current date on the same route.

---

All we have to do is to translate the above rules into code (DAX):

We need to know the last date for the route (For rule 3). Here I named it MaxDate:

MaxDate =
CALCULATE(
    MAX(Route[Date]),
    FILTER(
        Route,
        Route[ID] = EARLIER(Route[ID])
    )
)

We also need to know the next date for the same route (Rule 4):

NextDate =
CALCULATE(
    MIN(Route[Date]),
    FILTER(
        Route,
        Route[ID] = EARLIER(Route[ID]) &&
        Route[Date] > EARLIER(Route[Date])
    )
)

Now we just need to add the logic to return the location:

Destination = 
IF(
    Route[Date] = Route[MaxDate],
    Route[Location],
    CALCULATE(
        LASTNONBLANK(Route[Location], ""),
        FILTER(
            Route,
            Route[ID] = EARLIER(Route[ID]) &&
            Route[Date] = EARLIER(Route[NextDate])
        )
    )
)

And actually, you can use VAR to wrap it all and remove the interim date columns (The steps above are just for better understanding):

Destination = 
VAR MaxDate =
CALCULATE(
    MAX(Route[Date]),
    FILTER(
        Route,
        Route[ID] = EARLIER(Route[ID])
    )
)
VAR NextDate =
CALCULATE(
    MIN(Route[Date]),
    FILTER(
        Route,
        Route[ID] = EARLIER(Route[ID]) &&
        Route[Date] > EARLIER(Route[Date])
    )
)
RETURN
IF(
    Route[Date] = MaxDate,
    Route[Location],
    CALCULATE(
        LASTNONBLANK(Route[Location], ""),
        FILTER(
            Route,
            Route[ID] = EARLIER(Route[ID]) &&
            Route[Date] = NextDate
        )
    )
)