Filtering value based on next closest date

Question

Hi,&nbsp;I have a huge table with transport data.&nbsp;Route - is column with initial routeDesired output route - is column I would like to calculate which is&nbsp;next route taken by driver&nbsp;based on:&nbsp; &nbsp; &nbsp;1. Filtering by registration plate (has to be exact same).&nbsp; &nbsp; &nbsp;2. Dates - filter only loads taken within 2 days of previous unloading date.&nbsp;RouteLoading dateUnloading dateRegistration plateDesired output dateDesired output RouteChicago - Ontario07-11-201908-11-20191ABCno load takenno load takenAtlanta - New York14-11-201915-11-20191ABC15-11-2019New York - ChicagoNew York - Chicago15-11-201918-11-20191ABC18-11-2019Chicago - OntarioChicago - Ontario18-11-201919-11-20191ABC19-11-2019Los Angeles - New YorkLos Angeles - New York19-11-201920-11-20191ABC&nbsp;&nbsp;&nbsp;I tried below formula but it shows incorrect value:&nbsp;Desired output date = CALCULATE(FIRSTNONBLANK(Table1[Loading date];1);FILTER(ALL(Table1);Table1[Loading date]&lt;=[Unloading date]+2))&nbsp;&nbsp;&nbsp;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 Answer

Hi&nbsp;zuzaarbuza&nbsp;&nbsp;See if the following DAX statement for calculated column works for:&nbsp;&nbsp;Next route = 
VAR RegistrationPlate = RouteTable[Registration plate]
VAR UnloadingDate = RouteTable[Unloading date]
RETURN
    MAXX (
        
        TOPN ( 1,
            FILTER (
                RouteTable,
                RouteTable[Registration plate] = RegistrationPlate
                    &amp;&amp; RouteTable[Unloading date] &gt; UnloadingDate
                    &amp;&amp; RouteTable[Loading date]
                        &lt;= DATE ( YEAR ( UnloadingDate ), MONTH ( UnloadingDate ), DAY ( UnloadingDate ) + 2 )
            ),
            RouteTable[Loading date], ASC
        ), RouteTable[Route]
    )&nbsp;&nbsp;&nbsp;&nbsp;Let us know if that was helpful.&nbsp;Best&nbsp;David

Forum Discussion

Filtering value based on next closest date

2 Replies

Related content

Search for closest value

Calculate the value closest to selected date range

Lookup closest value to a number

Approximate lookup of closest values

Applying Color to the Closest Value

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