Filtering value based on next closest date

[zuzaarbuza]

Hi,

I have a huge table with transport data. 

Route - is column with initial route

Desired output route - is column I would like to calculate which is next route taken by driver based on:

     1. Filtering by registration plate (has to be exact same).

     2. Dates - filter only loads taken within 2 days of previous unloading date.

Route	Loading date	Unloading date	Registration plate	Desired output date	Desired output Route
Chicago - Ontario	07-11-2019	08-11-2019	1ABC	no load taken	no load taken
Atlanta - New York	14-11-2019	15-11-2019	1ABC	15-11-2019	New York - Chicago
New York - Chicago	15-11-2019	18-11-2019	1ABC	18-11-2019	Chicago - Ontario
Chicago - Ontario	18-11-2019	19-11-2019	1ABC	19-11-2019	Los Angeles - New York
Los Angeles - New York	19-11-2019	20-11-2019	1ABC

I tried below formula but it shows incorrect value:

Desired output date = CALCULATE(FIRSTNONBLANK(Table1[Loading date];1);FILTER(ALL(Table1);Table1[Loading date]<=[Unloading date]+2))

I would very much appreciate your help - I tried multiple lookup values and first non blank but have huge troubles with the syntax.

[Geradav · Accepted solution]

Hi @zuzaarbuza 

See if the following DAX statement for calculated column works for:

Next route = 
VAR RegistrationPlate = RouteTable[Registration plate]
VAR UnloadingDate = RouteTable[Unloading date]
RETURN
    MAXX (
        
        TOPN ( 1,
            FILTER (
                RouteTable,
                RouteTable[Registration plate] = RegistrationPlate
                    && RouteTable[Unloading date] > UnloadingDate
                    && RouteTable[Loading date]
                        <= DATE ( YEAR ( UnloadingDate ), MONTH ( UnloadingDate ), DAY ( UnloadingDate ) + 2 )
            ),
            RouteTable[Loading date], ASC
        ), RouteTable[Route]
    )

Let us know if that was helpful.

Best

David

[zuzaarbuza]

Hi @Geradav!

It worked just perfect, thank you so much! I will dig into how does it work

All best!