Lookupvalue returns only one value

[rabih]

I have to analyze one table Sales orders based on an older Sales table data. So for each record in the new table I have to find the closest delivered Sales order LastSO (primary key). I grouped the old Sales order in a table based on Customer Date and Maximum RecId for each date. There are no duplicate Deliver date/time and LastSO records. When I created a column ClosestSO as follows:

ClosestSO = LOOKUPVALUE(MaxRecIdByDateByCust[LastSO],
     MaxRecIdByDateByCust[Invoice account],NewOrders[Cust2Choose],
    MaxRecIdByDateByCust[Delivery date/time],LASTDATE
    (FILTER
        (ALL(MaxRecIdByDateByCust[Delivery date/time]),
            (MaxRecIdByDateByCust[Delivery date/time])<=NewOrders[DeliveryDate]
        )
    )
) 

I only get one record. Any idea why. I used the same logic for Exchange rates and it worked well

[v-ljerr-msft]

Hi @rabih,

Could you try using the formula below to see if it works? 

ClosestSO =
LOOKUPVALUE (
    MaxRecIdByDateByCust[LastSO],
    MaxRecIdByDateByCust[Invoice account], NewOrders[Cust2Choose],
    MaxRecIdByDateByCust[Delivery date/time], LASTDATE (
        FILTER (
            ALL ( MaxRecIdByDateByCust ),
            ( MaxRecIdByDateByCust[Delivery date/time] ) <= NewOrders[DeliveryDate]
                && MaxRecIdByDateByCust[Invoice account] = NewOrders[Cust2Choose]
        )
    )
)

Regards

[rabih]

I have copied and pasted your code snippet.

Got following error:

Something's wrong with one or more fields:(NewOrders) ClosestSO:

A table expression containing more than one column was specified in the call to function 'LASTDATE'. This is not supported.

[v-ljerr-msft · Accepted solution]

Hi @rabih,

My mistake. 

Could you try the formula below to see if it works?

ClosestSO =
LOOKUPVALUE (
    MaxRecIdByDateByCust[LastSO],
    MaxRecIdByDateByCust[Invoice account], NewOrders[Cust2Choose],
    MaxRecIdByDateByCust[Delivery date/time], CALCULATE (
        LASTDATE ( MaxRecIdByDateByCust[Delivery date/time] ),
        FILTER (
            ALL ( MaxRecIdByDateByCust ),
            ( MaxRecIdByDateByCust[Delivery date/time] ) <= NewOrders[DeliveryDate]
                && MaxRecIdByDateByCust[Invoice account] = NewOrders[Cust2Choose]
        )
    )
)

Regards

[rabih]

You nailed it 🙂