Power Pivot Dax measure previous item

[dicken]

Hi, I have the following measure to return previous row item based on consecutive date, work ok,

PrevItem:=VAR maxd = MAX(Table1[Date]) 
RETURN LOOKUPVALUE(Table1[Item], Table1[Date],maxd-1)

But when it's dropped in a pivot table its first value is filtered out, I'd like to keep it.
I know I could use pivot table options show items with no data, but would like to incorporate this into t the measure;
Ive tried using and IF statement, so IF measure returns a result , true, use the measure else put in a blank, but even though this has worked in the past
in this case it caused an error.

Richard.

 

[RoryA]

LOOKUPVALUE already returns BLANK if nothing matches - that's why you're not seeing the item. If you don't want to use the pivot table options, you need to return something.

 

[dicken]

I just needed to re-work things a bit and use a "" zero length txt string rather than the blank() ,
so
PrevItem :=

VAR oneItem =
    IF ( HASONEVALUE ( Table1[Date] ), VALUES ( Table1[Date] ) )
VAR less1 =
    MAXX ( FILTER ( ALL ( Table1[Date] ), Table1[Date] < oneItem ), Table1[Date] )
VAR lkup =
    LOOKUPVALUE ( Table1[Item], Table1[Date], less1 )
RETURN
    IF ( lkup = BLANK (), "", lkup )

Thanks for the pointers,
RD

 

[RoryA]

You should be able to use the final argument of LOOKUPVALUE:

LOOKUPVALUE ( Table1[Item], Table1[Date], less1, "" )

 

[dicken]

You should be able to use the final argument of LOOKUPVALUE:

LOOKUPVALUE ( Table1[Item], Table1[Date], less1, "" )

Thanks, didn't realise there was an alternate result will try it.

rd