cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dancarr22 Regular Visitor
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

PriceDiff.PNG

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor
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. Smiley Happy

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

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

 

v-ljerr-msft Super Contributor
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. Smiley Happy

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 Visitor
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%. 

 

image.png

Cheers!

Super User
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?