cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

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

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

4 REPLIES 4
Super User

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

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

Super Contributor

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

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

Visitor

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

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!

Super User

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

Hi,

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