Answered by:
DAX expression needed: First and last value of an attribute in a filtered time span
Question

Hello,
I have a (probably) simple problem concerning a DAX formula:
I have a table with share prices per day with three attributes: Date, share price, and the name of the share
Now I want to visualize the win or loss of a filtered time span (for example 01.01.201531.12.2015 or 01.03.201631.03.2016) The filtering is of course not the problem. However I can´t catch the difference of the share prices of the first day and the last of the time filter.
My Idea was to use the DAX formulas firstnonblank and lastnonblank to find the share price of the first day respective the last day.
Problem: I always get the min and the max of the share prices in the defined time span.
Which formula(s) can i use to solve this? Or do simply have a wrong order?
Thx for your help :)
Monday, May 23, 2016 7:11 PM
Answers

You are probably calculating the min and max of the share price instead of the min and max of the dates. Try using a time intelligence function:
SharePrice:=AVERAGE(Table[share price])
SharePriceFirstDay:=CALCULATE([SharePrice],FIRSTDAY(Calendar[Date]))
SharePriceLastDay:=CALCULATE([SharePrice],LASTDAY(Calendar[Date]))
You'll need a separate Calendar table with a row per day, and relate your table with share prices to the Calendar table. Also, the assumption here is that you have a share price for each day, otherwise you might end up with a blank result (this can be solved easily, but the approach is somewhat different).
 Proposed as answer by Darren GosbellMVP Wednesday, May 25, 2016 4:32 AM
 Marked as answer by Charlie Liao Saturday, June 4, 2016 8:41 AM
Tuesday, May 24, 2016 6:38 AMAnswerer
All replies

Excel 2010 with free PowerPivot AddIn.
Compatible with Office 2013/2016 Pro Plus.
Difference between extremes of time periods.
http://www.mediafire.com/download/dv8px7rqza4v3i1/05_23_16.xlsx
http://www.mediafire.com/download/7axpy7jued71vfq/05_23_16.pdfMonday, May 23, 2016 11:24 PM 
You are probably calculating the min and max of the share price instead of the min and max of the dates. Try using a time intelligence function:
SharePrice:=AVERAGE(Table[share price])
SharePriceFirstDay:=CALCULATE([SharePrice],FIRSTDAY(Calendar[Date]))
SharePriceLastDay:=CALCULATE([SharePrice],LASTDAY(Calendar[Date]))
You'll need a separate Calendar table with a row per day, and relate your table with share prices to the Calendar table. Also, the assumption here is that you have a share price for each day, otherwise you might end up with a blank result (this can be solved easily, but the approach is somewhat different).
 Proposed as answer by Darren GosbellMVP Wednesday, May 25, 2016 4:32 AM
 Marked as answer by Charlie Liao Saturday, June 4, 2016 8:41 AM
Tuesday, May 24, 2016 6:38 AMAnswerer 
Thank you very much for the fast help!Thursday, May 26, 2016 7:00 PM

Also for this answer > thx! Both ways workedThursday, May 26, 2016 7:00 PM

Hi Bigmuller,
Please don't forget to mark any of the responses that answered your question as the answer by using the 'Mark as answer' link.
Regards,
Michael Amadi
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)
Website: http://www.nimblelearn.com, Twitter: @nimblelearnMonday, May 30, 2016 8:39 PM