Latest Value not Aggregate of Daily Stock Price for Months/Quarters/Years - Hierarchy Value

[SAB3_User]

Hello, all! I created a simple visual tracking the price of multiple stocks by day and was hoping to make it so that when I clicked on the "drill up" button on the visual to zoom out to months, quarters, and years, it would show the "latest value" within each hierarchy period (month end; quarter end; yearly) rather than summing all of the values within those units.

For example, when I click the "drill up" from days to months, the value it shows for each month is the sum of the daily stock prices for the 28–31 days in that month rather than the last price of each month. I know how to adjust it to show correctly by filtering for only the ending dates each period after drilling up or down, but that is not an ideal solution (having the other users need to filter dates each time they want to drill up or down). 

Is there a simple way to make it so that visuals dynamically display the last value when drilling up and down in hierarchies? If I disable hierarchies it plots correctly, but removes the ability to drill up or down. Any help or insights would be wonderful!

[danno]

I assume you are using implicit measures in your visual, i.e. taking a numeric column and dragging it onto the chart?  If so, you have a limited number of options of how to aggregate the column.  You can choose Average, Min, Max etc.  

What you need to do is create a measure that controls what values are displayed, if you want to always return a total, then create a measure using SUM({column name}).   If you always want to show the last value for the period selected, then you could try the LASTNONBLANK function within a new measure.  Now this function does look simple, however there is a little more to it than meets the eye, as explained on this blog: https://exceleratorbi.com.au/lastnonblank-explained/

depending on your data, and how your tables are set up, you may find one of the patterns in this article works for you.  I would try the simple option given that you are using it in a date based visual, which is already sorted by date:  LastNonBlank = LASTNONBLANK('Data'[Value], 1)  - the 1 is included as the last parameter in the lastnonblank function is mandatory, see the article for more explanation.