cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
setis Member
Member

Lookup on same table (last date)

Dear All, 

 

I have a table with the following structure:

 

DateInvoiceDocumentAmount
02-01-2018 1224700
07-01-2018 12231500
05-01-20181223 -1000
06-01-20181223 -500
08-01-20181224 -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:

 

DateInvoiceDocumentAmountDate2
02-01-2018 122470008-01-2018
07-01-2018 1223150006-01-2018
05-01-20181223 -1000 
06-01-20181223 -500 
08-01-20181224 -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?
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Lookup on same table (last date)

@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] )
        )
    )
)

 

View solution in original post

4 REPLIES 4
Super User
Super User

Re: Lookup on same table (last date)

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   www.daxformatter.com

setis Member
Member

Re: Lookup on same table (last date)

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. 

Highlighted
Super User
Super User

Re: Lookup on same table (last date)

@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] )
        )
    )
)

 

View solution in original post

setis Member
Member

Re: Lookup on same table (last date)

@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!

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (1,086)