Calcualte Difference/% Change between two rows.

[Anonymous]

Hi there,

I have the following data:

Date  SPX Price    Volume

7/9/2020	3152.05	4829020000
7/8/2020	3169.94	4927700000
7/7/2020	3145.32	4563700000
7/6/2020	3179.72	4736450000
7/2/2020	3130.01	4190830000
7/1/2020	3115.86	4443130000
6/30/2020	3100.29	4696280000
6/29/2020	3053.24	4462770000
6/26/2020	3009.05	8098120000
6/25/2020	3083.76	4815420000
6/24/2020	3050.33	5587200000
6/23/2020	3131.29	4704830000
6/22/2020	3117.86	4665380000
6/19/2020	3097.74	8327780000
6/18/2020	3115.34	4429030000
6/17/2020	3113.49	4549390000
6/16/2020	3124.74	5829240000
6/15/2020	3066.59	5740660000
6/12/2020	3041.31	5832250000
6/11/2020	3002.1	7018890000
6/10/2020	3190.14	6570840000
6/9/2020	3207.18	6382620000
6/8/2020	3232.39	8437380000

I want to add a third column that calculates the % change in Price between two adjacent dates. Is this possible? Thanks.

[az38]

Hi @Anonymous 

try a measure like

Measure = 
var _prevDate = CALCULATE(MAX(Table[Date]), Table[Date] < MAX(Table[Date]))
var _prevPrice = CALCULATE(MAX(Table[SPX Price]), Table[Date] = _prevDate )
RETURN
DIVIDE(MAX(Table[SPX Price]), _prevPrice)

[Anonymous]

"A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

That is the error that comes with the measure.

[az38]

@Anonymous 

yes, sorry

Measure = 
var _curDate = MAX(Table[Date]) 
var _prevDate = CALCULATE(MAX(Table[Date]), Table[Date] < _curDate )
var _prevPrice = CALCULATE(MAX(Table[SPX Price]), Table[Date] = _prevDate )
RETURN
DIVIDE(MAX(Table[SPX Price]), _prevPrice)

[calerof]

Hi @az38,

I'm following your solution and don't get the previous price:

Could you please advise?

F

[az38]

@calerof 

it should work better

Measure = 
var _curDate = MAX('Table'[Date]) 
var _prevDate = CALCULATE(MAX('Table'[Date]), 'Table'[Date] < _curDate, ALL('Table') )
var _prevPrice = CALCULATE(MAX('Table'[SPX Price]), 'Table'[Date] = _prevDate, ALL('Table') )
RETURN
DIVIDE(MAX(Table[SPX Price]), _prevPrice)

[az38]

@calerof 

it's not exactly my solution. Your _Price is calculated for current row context. The same is about _prevDate

[calerof]

@az38 I don't get your last comment. My formula is exactly as the one you posted, therefore I thought it was your solution. It doesn't get the correct result:

[amitchandak]

@Anonymous ,

As a new colum

maxx(filter(table, [date] =earlier([Date])-1),[SPX Price])

As a measure

This Day = CALCULATE(max([SPX Price]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
This Day = CALCULATE(max([SPX Price]), previousday('Date'[Date]))

[amitchandak]

@Anonymous ,  

New columns if dates are not continous. In case date are continous the first formula I provided should be user. Earlier is powerful but costly. So should reduce the scope. 

last Date = maxx(filter(table, [date] <earlier([Date])-1),[[date]])
last Value = maxx(filter(table, [date] =earlier(last Date)),[SPX Price])

Measure Non continous 

Last Day Non Continous = CALCULATE(max([SPX Price]),filter(all('Date'),'Date'[Date] =MAXX(FILTER(all('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])))

[calerof]

@amitchandak,

I have a problem with the last expression of the formula: "table[Date]", it doesn't fit, as the rest of the elemtents are calling the Date table and this last one is the date in the fact table.

F

[amitchandak · Accepted solution]

@calerof , the correct version. Value replacement issue in my formula

Last Day Non Continuous = CALCULATE([sales],filter(ALLSELECTED('Date'),'Date'[Date] =MAXX(FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])))

[calerof]

@amitchandak 

Sorry, I checked it and didn't show the values on sparse dates, see:

[calerof]

Hi @amitchandak,

Your solution doesn't calculate the previous price if there is a gap between current date and the previous day of more than one day: