cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
SAB3_User New Member
New Member

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

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!

1 REPLY 1
danno Member
Member

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

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. 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)