Forum Discussion
Stock portfolio - working with missing quotes
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.
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
2 Replies
- barritown
Solution Sage
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
Amazing, thank you very much! 🙂
