# 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.2015-31.12.2015 or 01.03.2016-31.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?

Monday, May 23, 2016 7:11 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).

Tuesday, May 24, 2016 6:38 AM

### All replies

• Excel 2010 with free PowerPivot Add-In.
Compatible with Office 2013/2016 Pro Plus.
Difference between extremes of time periods.

Monday, 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).

Tuesday, May 24, 2016 6:38 AM
• Thank you very much for the fast help!
Thursday, May 26, 2016 7:00 PM
• Also for this answer -> thx! Both ways worked
Thursday, May 26, 2016 7:00 PM
• Hi Bigmuller,