DAX function for percentage change

[Paffius]

HI,

Imagine I want to create a formula that gives me the percentage change between to specific dates, and I want to do it within the powerpivot contexto (with DAX functions).

For examplejust for the sake of the example, these numbers are false)

A table with all the prices of Apple's stock during the year of 2008 and 2009.

APPL stock price was of $73.21 on August 3rd 2008 and a bit down in the same table it is quoting at $45.6 on November 15th 2009.

2 questions:

1)How to create a formula that gives me the percentage change between that specific period?

2) Because i want to analyze several "specific periods" over several years, I wanted to give a specific name to each of these periods. For example, I would like to cal the above mentioned period as "Financial Crisis 2008", so I can later use it as a filter used in a Pivot table. How to create these names for specific periods?

Thank you

 

[gazpage]

DATESADD shifts dates in the current context by a fixed amount. e.g.

CALCULATE (
    [ValueMeasure],
    DATEADD(DateTime[DateKey],-1,year)
)

would return the measure value with the dates moved back by 1 year.

If you want to go back to a specific date then.

CALCULATE (
    [ValueMeasure],
    Dates[Date] = "1/1/2015"
)

Would return the value at a specific date.

To be able to select between options, look up "disconnected slicers".

 

[Paffius]

But, can I make a formula that gives me a period between two specific dates? Like From January 1st 2008 to March 11th 2008?

And, as a side question, can i use this period as a filter in a slicer?

 

[Norie]

Can't you use LOOKUPVALUE to get the values for the 2 dates and then calculate the percentage difference?

 

[Paffius]

Can you exemplify?

 

[gazpage]

https://www.sqlbi.com/articles/alternative-use-of-firstnonblank-and-lastnonblank/

Look at the second set of DAX code here. Replicate, but with whatever you are trying to calculate in place of SUM <code class="dax parenthesis">(</code> <code class="dax plain">Inventory[UnitsBalance] </code><code class="dax parenthesis">)</code>.

Do the same but replace LASTNONBLANK with FIRSTNONBLANK.

Create measure that divides these two measures and this will now be the percentage difference between the value on the first and last date in your current date context. Add a date slicer and you should be good to go. Would need to understand your exact data structure to help further.

 

[Paffius]

https://www.sqlbi.com/articles/alternative-use-of-firstnonblank-and-lastnonblank/

Look at the second set of DAX code here. Replicate, but with whatever you are trying to calculate in place of SUM <code class="dax parenthesis">(</code> <code class="dax plain">Inventory[UnitsBalance] </code><code class="dax parenthesis">)</code>.

Do the same but replace LASTNONBLANK with FIRSTNONBLANK.

Create measure that divides these two measures and this will now be the percentage difference between the value on the first and last date in your current date context. Add a date slicer and you should be good to go. Would need to understand your exact data structure to help further.

Thank you so much for your help.
My date stucture would be as I said above, between two specific dates each one with diferente stock prices.

 

[gazpage]

FirstValue = 
CALCULATE (
    SELECTEDVALUE ( SourceData[Price] ),
    FIRSTNONBLANK ( 
        SourceData[Date],
        CALCULATE ( SELECTEDVALUE ( SourceData[Price] ) )
    )
)

LastValue = 
CALCULATE (
    SELECTEDVALUE ( SourceData[Price] ),
    LASTNONBLANK ( 
        SourceData[Date],
        CALCULATE ( SELECTEDVALUE ( SourceData[Price] ) )
    )
)

ChangeInValue = 
DIVIDE (
    [LastValue],
    [FirstValue]
) - 1

Edit: Then add a slicer on your date column

 

[Paffius]

Once again, thanks man!