Stock portfolio - working with missing quotes

[Fda]

Hi,

I am trying to create a stock portfolio report, but some of the desired stock does not always list a closing price.

Example:

The workaround I need is to list the latest known stock price, i.e. a merge of sort that checks if the value is blank, then returns the last known before that date.

Like this:

I have tried LASTNONBLANK and LASTNONBLANKVALUE, but I'm unable to sort of fill in - it just returns the latest known all the way.

I am in the desktop version, both solutions using columns and measures are fine.

[barritown · Accepted solution]

Hello @Fda,

If you want to solve your problem with [DAX], I can propose such a calculated column:

And the same in plain text:

Stock 1 w/o BLANKS = 
VAR CurrentDate = [Date]
VAR LastNonBlankDate = MAXX ( FILTER ( ALL ( data ), AND ( [Date] <= CurrentDate, NOT ISBLANK( [Stock 1] ) ) ), [Date] )
RETURN  LOOKUPVALUE ( data[Stock 1], data[Date], LastNonBlankDate )

There is also an option to solve your problem via Power Query:

1) You sort your table by date.

2) Right click on the column "Stock 1" (or "Stock 2") -> Fill -> Up.

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[Fda]

Amazing, thank you very much! 🙂