How to calculate percentage change from prior date (prior record)?

[dancarr22]

Hello,

How to calculate percentage change from prior date (prior record)?

So, in the example below we would want the 9/19 price minus the 9/18 price divided by the 9/18 price.  (144.39-144.55)/144.55.  then want this to work for every day (besides first day because there was no prior record)

This was very simple in Tableau - just had 'Percent Difference' as an option when displaying values.

Did see a 'Percent Different in 'Quick MEasures' but this seems to be looking for 2 different numeric fields.

Thanks,

Dan

[v-ljerr-msft · Accepted solution]

Hi @dancarr22,

Could you try using the formula below to create a new measure to see if it works in your scenario. 

Measure =
VAR previousDateWithPrice =
    CALCULATE (
        MAX ( Data[Date] ),
        FILTER ( ALL ( Data ), Data[Price] > 0 && Data[Date] < MAX ( Data[Date] ) )
    )
VAR previousPrice =
    CALCULATE (
        SUM ( Data[Price] ),
        FILTER ( ALL ( Data ), Data[Date] = previousDateWithPrice )
    )
RETURN
    DIVIDE ( SUM ( Data[Price] ) - previousPrice, previousPrice )

Remark: You'll need to replace the bold with your real table name and column name.

Regards

[mommabird]

I have a similar problem to which this has been the closest solution I've found so far, but it's still not quite achieving what I need. I'm working with educational data which is parsed out in terms (i.e. 2015SP, 2015SU, 2015FA, 2016SP, 2016SU, etc.). For each term there are over a 1,000 sections for which we need to calculate total full-time equivalent enrollments and then compare the total enrollments from Fall to Fall and Spring to Spring. 

How would I adjust your code to achieve this? Here's an example of a matrix I'm trying to create. The values in the measure field are not correct. They should be -4.3% and -4.0%. 

Cheers!

[Ashish_Mathur]

Hi,

Share some data and show the expected result - how does only know when the term starts/ends?

[Ashish_Mathur]

Hi,

Try this calculated field formula

=IFERROR([Amount]/CALCULATE(LASTNONBLANK(Data[Price],[Amount]>0),Data[Date]<MAX(Calendar[Date])),BLANK())

Here are my assumptions

1. Amount is a calculated field formula =SUM(Data[Price])
2. There is a calendar table.  The date column in the Data table has a relatiosnhip to the Date column of the Calendar table
3. Date in the visual have been dragged from the Calendar table

If my solution does not work, then share the link from where i can download your file.