Find closest dates that contains value sorted on ID

[Jonas1]

Hey!

Been trying for a long time now to figure out how I can write dax which: 

1. Finds the row that are empty (should have contained a string, which is the name of a project)

2. Evaluates which non-empty rows that are not empty, have the same ID and are closest to the empty row in time

3. Then fills out the empty row with the project name that is considered the best match. 

ID	Date	Project
10139	31.10.2019	Project 1
10137	01.11.2019	Project 2
10137	15.11.2019	[Empty row]

I have achieved to write a formula which finds the closest non-empty date sorted on ID, but how do I make it change ID number based on which row it is evaluating? 

My code so far: 

    CALCULATE (
        LASTNONBLANK ( 'Kjøretøydata'[Project]; 1 );
        FILTER (
            'Table1';
            'Table1'[Date]<= EARLIER ('Table1'[Date] )
            && NOT ( ISBLANK ('Table1'[Project]))
            && 'Table1'[ID]="10137")
    )

So the only thing missing now is replacing "10137" with some DAX that dynamically takes the value of the row being evaluated. Any tips on what function I should use to achieve this? 

[Jonas1 · Accepted solution]

Showed out my first suggestion were pretty close to a solution. 

This gives you the closest date to any ID at any point in time: 

    CALCULATE (
        LASTNONBLANK ( 'Table1'[Project]; 1 );
        FILTER (
            'Table1';
            'Table1'[Date]<= EARLIER ('Table1'[Date] )
            && NOT ( ISBLANK ('Table1'[Project]))
            && 'Table1'[ID]=earlier('Table1'[ID]))
    )

In order to find the last registered project I could now filter on this date: 

Project = 
if(isblank('Table1'[Project]);
    CALCULATE(LASTNONBLANK('Table1'[Project];'Table1'[Project]); 
    FILTER('Table1';
    'Table1'[Date]=EARLIER('Table1'[ClosestDate])&&
    'Table1'[ID]=EARLIER('Table1'[ID])));
'Table1'[Project])

There were already several threads dealing with a similar issue, but none that extracted everything from the same table as far as I could see. 

[Anonymous]

@Jonas1 

Measure = 
VAR _maxdate = CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[ID]),'Table'[Project]<>BLANK())
RETURN CALCULATE(MAX('Table'[Project]),FILTER(ALLEXCEPT('Table','Table'[ID]),'Table'[Date]=_maxdate))

please try this measure 

[Jonas1]

Thanks for your suggestion Vimal. 

But it foes not work either unfortuantely as I want the formula to use the registered project at the closest date at any point in time, also when this is not the project registered last. 

Any idea how to achieve this?