Drill up in time showing LAST VALUE

[sbeste]

How can I get my displays to show closing stock prices regardless of whether the user drills in to show days, months, or years?

I have a table of daily closing prices for stocks, covering several years. I can easily create a matrix visual or line chart that shows the stock code in the rows, a column for each day, and the closing price in the table. Easy!

But now drill up so that the X-axis shows Months. What's the value for "closing stock price"? It's not the Sum of the daily prices, nor the Average - it's the value on the last day of each month. Likewise, when I drill up to show just years, the closing price for each year should be the value on the last day of each year (or part of the year if we're in the middle of the year and the data goes only through, say, November).

I assume I need to create a new measure that returns this value and is sensitive to the current drill-down level of the display (hence, last day of the month, last day of the year, whichever level is showing on the X-axis).

Is this possible with Power BI? I see stock charts like this all over the Internet. Thanks - Steve.

[sdjensen · Accepted solution]

Hi,

Reading this should give you what you need: https://www.sqlbi.com/articles/semi-additive-measures-in-dax/

[sbeste]

WOW! That article is exactly what I need. Two years ago, I was evaluating BI tools and put this problem to 9 vendors. None of their technical people could solve it. Most had never heard of it. And here's an answer in less than an hour thanks to you, Mr. Jensen and the Power BI community. Thank you! This is amazing. And as the article suggests, I'm not really working with stock prices but with inventory (monthly snapshots of staff on board). I just used the closing price example because it communicates better. Many thanks.