DAX formula to LOOKUP result based on Date AND ignoring blanks?

[GooseHelpful199]

I have a query that looks like Query 1, and I want to create a Query 2 where I duplicate Query 1, remove duplicates on the "Item" column, and then pull the latest "Case Size" result.

The issues with this are that LOOKUPVALUE wont work with multiple results, I need to only use the latest result, and that I need to ignore results that are blank (without removing the data from Query 1). I believe that the solution requires utilizing FILTER, MAX, and FIRSTNONBLANK functions, but I am having trouble with creating a solution that works. Below is the closest thing I can think of that is similar to other formulas I've got to work, but I cant seem to get this one to work as intended. Any ideas on how to get this to work, or for a different solution altogether?

Latest Case Size =

VAR Latest_Time_=

    CALCULATE(

        MAX ( 'Query 1'[Date] ),

        FILTER ( ALL ( 'Query 1' ), 'Query 1'[Item] = 'Query 2'[Item])

    )

RETURN

CALCULATE (

    FIRSTNONBLANK ('Query 1'[Case Size], 1 ),

    FILTER ( ALL('Query 1'), 'Query 1'[Order Date] = Latest_Time_ )

)

[ryan_mayu · Accepted solution]

@GooseHelpful199 

pls try this

Measure = 
VAR _last=maxx(FILTER('Table','Table'[Item]=max('Table'[Item])&&not(ISBLANK('Table'[Case Size]))),'Table'[Date])
return maxx(FILTER('Table','Table'[Item]=max('Table'[Item])&&'Table'[Date]=_last),'Table'[Case Size])

pls see the attachment below

[GooseHelpful199]

This worked perfectly, thanks!

[ryan_mayu]

you are welcome