Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Fda
Frequent Visitor

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:

Fda_2-1685527966401.png

 

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:

Fda_1-1685527938710.png

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.

1 ACCEPTED SOLUTION
barritown
Super User
Super User

Hello @Fda,

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

barritown_0-1685542437861.png

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.

 

barritown_1-1685542705940.png

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

View solution in original post

2 REPLIES 2
barritown
Super User
Super User

Hello @Fda,

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

barritown_0-1685542437861.png

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.

 

barritown_1-1685542705940.png

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

Fda
Frequent Visitor

Amazing, thank you very much! 🙂 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.