Lookup on same table (last date)

[setis]

Dear All, 

I have a table with the following structure:

Date	Invoice	Document	Amount
02-01-2018		1224	700
07-01-2018		1223	1500
05-01-2018	1223		-1000
06-01-2018	1223		-500
08-01-2018	1224		-700

As you can see the link between lines is Invoice<->Document

I am looking for a measure or calculated column that gives me the latest Date in which [Invoice]=[Document]

The desired output in the example would be:

Date	Invoice	Document	Amount	Date2
02-01-2018		1224	700	08-01-2018
07-01-2018		1223	1500	06-01-2018
05-01-2018	1223		-1000
06-01-2018	1223		-500
08-01-2018	1224		-700

I've tried different approaches, one of those being: 

Date2 = LOOKUPVALUE(MAX(Table1[Date]);Table1[Document];Table1[Invoice])

but it's obiously not corrent. 

Could somebody please help me here?

[AlB]

Hi @setis

You were close, your approach makes sense, but LOOKUPVALUE requires a column in the first argument. Try this for a calculated column in your table:

Date2 =
CALCULATE (
    MAX ( Table1[Date] ),
    FILTER (
        Table1,
        Table1[Invoice] = EARLIER ( Table1[Document] )
    )
)

Code formatted with  

[setis]

Hi @AlB, thanks for your answer. 

This is giving me the error: "DAX comparison operations do not support comparing values of type True/False with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values."

The Error is probably because the column [Document] is formated as text. Some of the cells are text strings. Apologies for not mentioning this before. 

[AlB · Accepted solution]

@setis

Hmm... that's weird.  I just ran a quick test here and it seems to work fine.

1. Are you sure you've copied the code correctly? It sounds like there might be a misplaced comma or similar that is tricking the FILTER( ) function

2. Are [Document] and [Invoice] of the same type, both numbers or both text? They need to

Additionally, if you want Date2 to appear only in the rows where Document is non-blank, as you show in your example, we need a small modification to what I wrote previously:

Date2 =
IF (
    NOT ISBLANK ( Table1[Document] ),
    CALCULATE (
        MAX ( Table1[Date] ),
        FILTER (
            Table1,
            Table1[Invoice] = EARLIER ( Table1[Document] )
        )
    )
)

[setis]

@AlBThank you so much!! You were right. I had misplaced a comma and thanks a lot for the last addition to the code. This is a great learning!