Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
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! 🙂
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |