locked
DAX expression needed: First and last value of an attribute in a filtered time span RRS feed

  • 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?

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

    Tuesday, May 24, 2016 6:38 AM
    Answerer

All replies

  • Excel 2010 with free PowerPivot Add-In.
    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.pdf

    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
    Answerer
  • 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,

    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: @nimblelearn

    Monday, May 30, 2016 8:39 PM