Forum Discussion

Frequent Visitor

3 years ago

Solved

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...

Need Help

barritown
3 years ago
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

barritown

Solution Sage

3 years ago

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